VLOOKUP in VBA
On the worksheet I can insert in a cell
=VLOOKUP(C5, Hobokee.xls!AcsLow, 2)
and it works perfectly, looks up the value in column 2 of the range named
AcsLow in the same workbook.
But elswhere I want in a macro to lookup the same table and assign the
result to a variable BalAmt.
BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work.
It gives a function not defined error on Hobokee. If instead of Hobokee I
put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP
which it changes to VLookup.
I have been overVLOOKUP in the Help file and see nothing wrong.
What is wrong ?
Stuart
|
0 |
|
Stuart
3/20/2006 10:06:58 AM
excel
39879 articles. 
2 followers.
4 Replies
3630 Views
VLookup is a worksheet function so you need to specify it as such; WorksheetFunction.VLookup(etc...)
barryjonunattendedDim res as variant
dim lookupRng as range
dim lookupVal as range
set lookuprng = workbooks("hobokee.xls").range("acsLow")
set lookupval = workbooks("otherbook.xls").worksheets("sheet99").range("c5")
res = application.vlookup(lookupval,lookuprng,2)
if iserror(res) then
'same as #n/a
msgbox "not found"
else
msgbox res
end if
Stuart Grant wrote:
>
> On the worksheet I can insert in a cell
> =VLOOKUP(C5, Hobokee.xls!AcsLow, 2)
> and it works perfectly, looks up the value in column 2 of the range named
> AcsLow in the same workbook.
> But elswhere I want in a macro to lookup the same table and assign the
> result to a variable BalAmt.
> BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work.
> It gives a function not defined error on Hobokee. If instead of Hobokee I
> put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP
> which it changes to VLookup.
> I have been overVLOOKUP in the Help file and see nothing wrong.
> What is wrong ?
> Stuart
--
Dave Peterson
petersodBarry-Jon Thanks for your prompt help. Pity that when you look up VLookup in VBA help, there is no mention of WorkshopFunction. I had a little trouble with the range too but have got this sorted out. Stuart "Barry-Jon" <barryjonunattended@yahoo.co.uk> wrote in message news:1142853196.233182.97280@i40g2000cwc.googlegroups.com... > VLookup is a worksheet function so you need to specify it as such; > > WorksheetFunction.VLookup(etc...) >
|
|
0 |
|
Stuart
3/20/2006 3:02:30 PM
Look for worksheetfunction in VBA's help. But for any function, you'll find the Excel's help (not VBA's) is where you'll want to check. Stuart Grant wrote: > > Barry-Jon > > Thanks for your prompt help. Pity that when you look up VLookup in VBA > help, there is no mention of WorkshopFunction. > I had a little trouble with the range too but have got this sorted out. > > Stuart > > "Barry-Jon" <barryjonunattended@yahoo.co.uk> wrote in message > news:1142853196.233182.97280@i40g2000cwc.googlegroups.com... > > VLookup is a worksheet function so you need to specify it as such; > > > > WorksheetFunction.VLookup(etc...) > > -- Dave Peterson
Similar Artilces:
using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...
changing position and width of textfields with vba
hello, is there any way to change the positions (left.coordinate) and width of textfields with vba while opening a report? Problem: i have 10 textfields and the user can decide, how many of them should be printed. so they can say: print column 1,2,5,6 or 3,6,8 or whatever. In the report i wanna change the position of the textfields so in example one the field 1 start at left-position 0 and have a width of maxwidth/4. In second example field 3 have to start at left-position 0 and should have the width of maxwidth/3. it seems at the report-events if cant change that attributes while usin...
vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...
vba error after moving access db
I have this code: Private Sub List49_DblClick(Cancel As Integer) Dim stDocName As String Dim stLinkCriteria As String stDocName = "ActiveEEList" stLinkCriteria = "[EmpID]=" & Me![List49].Column(1) DoCmd.OpenForm stDocName, , , stLinkCriteria End Sub It was working fine until I moved the access db from one path to another...I had it in MY Docs folder then moved (copied) it to a network drive. When I run if from the old version it works fine, but now from the new location I get Run Time Error 3075, Syntax Error (missing operator) in query expression [EmplID]...
Open a DB connection in VBA
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C3BB61.8EB14570 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Can anyone tell me where I get some info / examples of connecting to a = DB via MS Excel VBA programming. I have done a bit of VBA before but have never tried connecting to an = external DB. I would like some examples please. Personal or web sites = gratefully accepted. Thanks in advance. Dean --=20 Regards Dean=20 dkso@ntlworld.com www.dkso.co.uk/ http://homepage.ntlworld.com/dkso ------=_NextPart_...
Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...
Adding to VLOOKUP table
I have a table to which I have added 2 further columns I have a formula which looks up colums 2 and 3 when I key data in column 1 =if(trim(a23)="","",vlookup(a23,scanner_table,2,0)) The new data I want to select is in column 5 so =if(trim(a24)="","",vlookup(a24,scanner_table,5,0)) to which I get the raspberry #REF I am sure this must be very basic but sometimes I can't see the wood for trees Any comments would be much appreciated. Hi Billy Have you extended the range definition for scanner_table to include the additional 2 columns? Regards R...
VBA code #4
Does anyone know what is the code for adding a text message in an emai VBA spreadshee -- billy2will ----------------------------------------------------------------------- billy2willy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2164 View this thread: http://www.excelforum.com/showthread.php?threadid=39235 I am sure you will find it at www.rondebruin.nl/sendmail.htm -- HTH RP (remove nothere from the email address if mailing direct) "billy2willy" <billy2willy.1t5qee_1123020307.8914@excelforum-nospam.com> wrote in message news:billy2wil...
is my excel corrupt? VLOOKUP
I was having problems with a huge spreadsheet using VLOOKUP so I tried a small simple table and I'm getting some strange results can someone have a look here http://www.naldernet.plus.com/holding/vlookup.xls and explain why if you type "horn" at B14 does it return the result of B5 ? thanks in advance probably something really stupid I missed in the VLOOKUP formula -- Vass "Vass" <mark.nalder_TRAPTHESPAM_@btinternet.com> wrote in message news:EuqdnTraeqlpM3XfRVnyuw@eclipse.net.uk... > I was having problems with a huge spreadsheet using VLOOKUP > so I t...
Creating a string from a query in vba
using access 2003 i have a qry that returns top 5 values 1 Item1 123 2 Item2 101 3 Item3 32 4 Item4 2 5 Item5 76 im trying to create a function that retuens a string like "Item1 123, Item2 101, Item3 32" etc etc I can create a function that returns the first row but not all 5 rows. can anyone help please?? One option is here: http://allenbrowne.com/func-concat.html Another here: http://www.rogersaccesslibrary.com/forum/topic16.html They are similar, but one may work better than the other for your specific situation. StuJol wrote:...
I want that vlookup gets value once
I do not know how to express myself with my poor english but I have a case that vlookup getting value from another sheet which takes values from Internet (euro/usd rate). But I want vlookup gets the value once and not each time that workbook is opened. I am getting always the same rate for all records and I do not want this .. How to solve ? Please help Sheet1 : A1 A2 A3 RATE 01.Jan EUR 1,7 A3--> Formula:=VLOOKUP(A2;RATES!$E$10:$F$12;2;FALSE) 02.Jan EUR 1,7 03.Jan EUR 1,7 26.Jan EUR 1,7 Rate in Sheet RATES! is changing daily. I want th...
VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...
Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...
Controls in VBA
Hello, i'd like to make more smart my controls in my VBA project. Instead of Windows Forms 2.0 which ist the primary option when using Windows Forms I am trying to use the controls of Microsoft Windows Common Controls 6.0 (SP6). In particular: ListView, ListviewControl, ImageList, ImageCombo... and I'm experiencing problems when using these controls. I´d like to show a list of mails and an special icon associated depending of the sender. This ist not possible with Windows Forms 2.0 and that's why i'm exploring this option. Is it maybe not recommend the ...
Trapping Excel Alerts in vba
Hi folks I'm refreshing loads of workbooks overnight in an automated trawl thru a list of directories. Any message boxes popping up block the job waiting for a manual reply. I don't want to use "excel.displayAlerts = false", because my desired approach is not to ignore alerts, but rather to abandon work on this workbook, log an error and proceed to the next workbook as soon as any alert pokes its head above the parapet. Here's a code snippet.... [code]On Error GoTo err ' refresh data ranges and pivot tables For Each ws In ActiveWorkbook.Worksheets For Each qt...
Adding VBA code to a form
I have created a form using a wizard. the code that appears looks like this Private Sub Enter_pg1_Click() On Error GoTo Err_Enter_pg1_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "Data_Capture_step_2" DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_Enter_pg1_Click: Exit Sub Err_Enter_pg1_Click: MsgBox Err.Description Resume Exit_Enter_pg1_Click End Sub Private Sub Exit_pg_1_Click() On Error GoTo Err_Exit_pg_1_Click DoCmd.Close Exit_Exit_pg_1_Click: Exit Sub Err_Exit_pg_1_Click: MsgBox Err.Description R...
using VBA to retrieve values to ComboBox
i am trying to retrieve a range of data (the stock code of different phones) from worksheet("Inventory") based on the name of the branches of where the phones are stored. worksheet("Inventory") has branch (Column A) and stock code (Column B) i have 2 combobox. combobox1 contains values (name of branches) such as: 1-BS 2-EN 3-HG 4-JE 5-SP 6-TB 7-WS 8-YT combobox2 will have to retrieve values from the worksheet("Inventory") based on the value in combobox1. the values in combobox1 can be found in column 1 while the values i need for combobox...
VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...
VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...
VLOOKUP
This is a multi-part message in MIME format. ------=_NextPart_000_0001_01CAC8EE.B1306170 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I use the VLOOKUP function to pull basic data from external data sheets, currently an example of my command looks like this: =VLOOKUP($A5,'[RT NP 67 MF.xls]ODD'!$A$1:$S$250,$L$1,FALSE) The "$AA%" is the data I am looking up and the "$L$1" is a variable to the column I am wishing to insert. The question I am trying to get an answer for concerns...
Updating tasks with VBA script
I've built a scheduling tool which uses Access to create the tasks. Once the tasks are generated, they're sent to Outlook. My problem is the tasks are grouped in categories and if a due date is changed it needs to move all the following tasks by x number of days. (My keyfield ie- 57-41, then 57-42, etc. is stored in the contacts field) I had planned to first pull the data back to Access to see what dates changed. Off of that data, I'd delete the following tasks and reinsert them with the modified dates. I would prefer to do this in Outlook but do not know the Outlook object mod...
Collecting Range Name values to VBA
I have a worksheet "Setup" where users type in a date in a cell named "ChtDte" and a path and database name in a cell named "FLName". I am using DOA to connect to a database and return a record set. The query used "qryCOCostwRates" uses a date paramater. Because this sheet will be used by several users all pointing to the database in different locations, I need to know where they have the database. I need to get the values in these two range names in the setup tab of the spreadsheet so I can connect to the data, and provide a value for ...
Convert VBA macro to Applescript
Version: 2008 I don't know if this is even possible. I have an existing VBA macro I use on Word for Windows and I need to get the equivalent functionality running on Word 2008 for Mac. This code needs to show/hide logos in the headers and will be used when the author decides to print onto letterhead or not. <br><br>The basic task breakdown is: <br> 1. Toggle the value of an existing boolean custom document property <br> 2. Search all graphic objects in the headers and if they have a specific text value, make the visibility of the graphic match the custom docume...
Why #NA when using VLOOKUP?
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C7C7AD.F7E2E420 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable I'm trying to use VLOOKUP to find lowest value in a small group. The = exact sample is below: A B 1 3.0001 A=20 2 2.9442 B=20 3 2.9610 C=20 4 2.9055 D=20 5 2.9630 E=20 The formula I'm using is =3DVLOOKUP(MIN(A1:A5),A1:B5,1) . I'm trying to = get it to return the lowest cost, from column A. If I take out Row 4, = it wo...
VLookup in other document
Here is my problem. Please help I have two documents A and B. In document A I have a table existing of codes and descriptions Range A1: B100 Column A contains codes, Column B contains descriptions In document B I want to lookup the description for a given code. I have a cell C10 containing the code and a cell C11 containing a formula =VLOOKUP(A1;'K:\Douane\Regressie\[RTScenario.xls]FxP'!$A$1:$B200;2) Till so far it works !! But I use this formula many times in my excel sheet and in many Excel documents, so if Ithe lokation of document A changes, I have to change all the for...
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...
changing position and width of textfields with vba
hello, is there any way to change the positions (left.coordinate) and width of textfields with vba while opening a report? Problem: i have 10 textfields and the user can decide, how many of them should be printed. so they can say: print column 1,2,5,6 or 3,6,8 or whatever. In the report i wanna change the position of the textfields so in example one the field 1 start at left-position 0 and have a width of maxwidth/4. In second example field 3 have to start at left-position 0 and should have the width of maxwidth/3. it seems at the report-events if cant change that attributes while usin...
vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...
vba error after moving access db
I have this code: Private Sub List49_DblClick(Cancel As Integer) Dim stDocName As String Dim stLinkCriteria As String stDocName = "ActiveEEList" stLinkCriteria = "[EmpID]=" & Me![List49].Column(1) DoCmd.OpenForm stDocName, , , stLinkCriteria End Sub It was working fine until I moved the access db from one path to another...I had it in MY Docs folder then moved (copied) it to a network drive. When I run if from the old version it works fine, but now from the new location I get Run Time Error 3075, Syntax Error (missing operator) in query expression [EmplID]...
Open a DB connection in VBA
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C3BB61.8EB14570 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Can anyone tell me where I get some info / examples of connecting to a = DB via MS Excel VBA programming. I have done a bit of VBA before but have never tried connecting to an = external DB. I would like some examples please. Personal or web sites = gratefully accepted. Thanks in advance. Dean --=20 Regards Dean=20 dkso@ntlworld.com www.dkso.co.uk/ http://homepage.ntlworld.com/dkso ------=_NextPart_...
Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...
Adding to VLOOKUP table
I have a table to which I have added 2 further columns I have a formula which looks up colums 2 and 3 when I key data in column 1 =if(trim(a23)="","",vlookup(a23,scanner_table,2,0)) The new data I want to select is in column 5 so =if(trim(a24)="","",vlookup(a24,scanner_table,5,0)) to which I get the raspberry #REF I am sure this must be very basic but sometimes I can't see the wood for trees Any comments would be much appreciated. Hi Billy Have you extended the range definition for scanner_table to include the additional 2 columns? Regards R...
VBA code #4
Does anyone know what is the code for adding a text message in an emai VBA spreadshee -- billy2will ----------------------------------------------------------------------- billy2willy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2164 View this thread: http://www.excelforum.com/showthread.php?threadid=39235 I am sure you will find it at www.rondebruin.nl/sendmail.htm -- HTH RP (remove nothere from the email address if mailing direct) "billy2willy" <billy2willy.1t5qee_1123020307.8914@excelforum-nospam.com> wrote in message news:billy2wil...
is my excel corrupt? VLOOKUP
I was having problems with a huge spreadsheet using VLOOKUP so I tried a small simple table and I'm getting some strange results can someone have a look here http://www.naldernet.plus.com/holding/vlookup.xls and explain why if you type "horn" at B14 does it return the result of B5 ? thanks in advance probably something really stupid I missed in the VLOOKUP formula -- Vass "Vass" <mark.nalder_TRAPTHESPAM_@btinternet.com> wrote in message news:EuqdnTraeqlpM3XfRVnyuw@eclipse.net.uk... > I was having problems with a huge spreadsheet using VLOOKUP > so I t...
Creating a string from a query in vba
using access 2003 i have a qry that returns top 5 values 1 Item1 123 2 Item2 101 3 Item3 32 4 Item4 2 5 Item5 76 im trying to create a function that retuens a string like "Item1 123, Item2 101, Item3 32" etc etc I can create a function that returns the first row but not all 5 rows. can anyone help please?? One option is here: http://allenbrowne.com/func-concat.html Another here: http://www.rogersaccesslibrary.com/forum/topic16.html They are similar, but one may work better than the other for your specific situation. StuJol wrote:...
I want that vlookup gets value once
I do not know how to express myself with my poor english but I have a case that vlookup getting value from another sheet which takes values from Internet (euro/usd rate). But I want vlookup gets the value once and not each time that workbook is opened. I am getting always the same rate for all records and I do not want this .. How to solve ? Please help Sheet1 : A1 A2 A3 RATE 01.Jan EUR 1,7 A3--> Formula:=VLOOKUP(A2;RATES!$E$10:$F$12;2;FALSE) 02.Jan EUR 1,7 03.Jan EUR 1,7 26.Jan EUR 1,7 Rate in Sheet RATES! is changing daily. I want th...
VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...
Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...
Controls in VBA
Hello, i'd like to make more smart my controls in my VBA project. Instead of Windows Forms 2.0 which ist the primary option when using Windows Forms I am trying to use the controls of Microsoft Windows Common Controls 6.0 (SP6). In particular: ListView, ListviewControl, ImageList, ImageCombo... and I'm experiencing problems when using these controls. I´d like to show a list of mails and an special icon associated depending of the sender. This ist not possible with Windows Forms 2.0 and that's why i'm exploring this option. Is it maybe not recommend the ...
Trapping Excel Alerts in vba
Hi folks I'm refreshing loads of workbooks overnight in an automated trawl thru a list of directories. Any message boxes popping up block the job waiting for a manual reply. I don't want to use "excel.displayAlerts = false", because my desired approach is not to ignore alerts, but rather to abandon work on this workbook, log an error and proceed to the next workbook as soon as any alert pokes its head above the parapet. Here's a code snippet.... [code]On Error GoTo err ' refresh data ranges and pivot tables For Each ws In ActiveWorkbook.Worksheets For Each qt...
Adding VBA code to a form
I have created a form using a wizard. the code that appears looks like this Private Sub Enter_pg1_Click() On Error GoTo Err_Enter_pg1_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "Data_Capture_step_2" DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_Enter_pg1_Click: Exit Sub Err_Enter_pg1_Click: MsgBox Err.Description Resume Exit_Enter_pg1_Click End Sub Private Sub Exit_pg_1_Click() On Error GoTo Err_Exit_pg_1_Click DoCmd.Close Exit_Exit_pg_1_Click: Exit Sub Err_Exit_pg_1_Click: MsgBox Err.Description R...
using VBA to retrieve values to ComboBox
i am trying to retrieve a range of data (the stock code of different phones) from worksheet("Inventory") based on the name of the branches of where the phones are stored. worksheet("Inventory") has branch (Column A) and stock code (Column B) i have 2 combobox. combobox1 contains values (name of branches) such as: 1-BS 2-EN 3-HG 4-JE 5-SP 6-TB 7-WS 8-YT combobox2 will have to retrieve values from the worksheet("Inventory") based on the value in combobox1. the values in combobox1 can be found in column 1 while the values i need for combobox...
VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...
VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...
VLOOKUP
This is a multi-part message in MIME format. ------=_NextPart_000_0001_01CAC8EE.B1306170 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I use the VLOOKUP function to pull basic data from external data sheets, currently an example of my command looks like this: =VLOOKUP($A5,'[RT NP 67 MF.xls]ODD'!$A$1:$S$250,$L$1,FALSE) The "$AA%" is the data I am looking up and the "$L$1" is a variable to the column I am wishing to insert. The question I am trying to get an answer for concerns...
Updating tasks with VBA script
I've built a scheduling tool which uses Access to create the tasks. Once the tasks are generated, they're sent to Outlook. My problem is the tasks are grouped in categories and if a due date is changed it needs to move all the following tasks by x number of days. (My keyfield ie- 57-41, then 57-42, etc. is stored in the contacts field) I had planned to first pull the data back to Access to see what dates changed. Off of that data, I'd delete the following tasks and reinsert them with the modified dates. I would prefer to do this in Outlook but do not know the Outlook object mod...
Collecting Range Name values to VBA
I have a worksheet "Setup" where users type in a date in a cell named "ChtDte" and a path and database name in a cell named "FLName". I am using DOA to connect to a database and return a record set. The query used "qryCOCostwRates" uses a date paramater. Because this sheet will be used by several users all pointing to the database in different locations, I need to know where they have the database. I need to get the values in these two range names in the setup tab of the spreadsheet so I can connect to the data, and provide a value for ...
Convert VBA macro to Applescript
Version: 2008 I don't know if this is even possible. I have an existing VBA macro I use on Word for Windows and I need to get the equivalent functionality running on Word 2008 for Mac. This code needs to show/hide logos in the headers and will be used when the author decides to print onto letterhead or not. <br><br>The basic task breakdown is: <br> 1. Toggle the value of an existing boolean custom document property <br> 2. Search all graphic objects in the headers and if they have a specific text value, make the visibility of the graphic match the custom docume...
Why #NA when using VLOOKUP?
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C7C7AD.F7E2E420 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable I'm trying to use VLOOKUP to find lowest value in a small group. The = exact sample is below: A B 1 3.0001 A=20 2 2.9442 B=20 3 2.9610 C=20 4 2.9055 D=20 5 2.9630 E=20 The formula I'm using is =3DVLOOKUP(MIN(A1:A5),A1:B5,1) . I'm trying to = get it to return the lowest cost, from column A. If I take out Row 4, = it wo...
VLookup in other document
Here is my problem. Please help I have two documents A and B. In document A I have a table existing of codes and descriptions Range A1: B100 Column A contains codes, Column B contains descriptions In document B I want to lookup the description for a given code. I have a cell C10 containing the code and a cell C11 containing a formula =VLOOKUP(A1;'K:\Douane\Regressie\[RTScenario.xls]FxP'!$A$1:$B200;2) Till so far it works !! But I use this formula many times in my excel sheet and in many Excel documents, so if Ithe lokation of document A changes, I have to change all the for...