Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup in a Macro
I am trying to write a macro that duplicates a data evaluation procedure
given to me from someone else. There are several steps requiring VLOOKUPs. I have been told that I cannot run a VLOOKUP within a macro? Is this a true statement. If I can do it, how? -- Sincerely, Beverly76 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup in a Macro
Hi
To use worksheet functions in VBA use this syntax: Application.WorksheetFunction.VLOOKUP(lookup_value ,table_array,col_index_num,range_lookup) BTW: It's not all worksheet functions which are available in VBA. Regards, Per On 8 Sep., 20:48, Beverly76 wrote: I am trying to write a macro that duplicates a data evaluation procedure given to me from someone else. *There are several steps requiring VLOOKUPs. * I have been told that I cannot run a VLOOKUP within a macro? *Is this a true statement. *If I can do it, how? -- Sincerely, Beverly76 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup in a Macro
VBA code will only do Exact matches using the VBA Find(). Bt you can call
the lokup worksheet functions (lookup, v lookup,hlookup) from VBA. Excel 2003 : worksheetfunctions.vlookup(...) Excel 2007 : application.vlookup(...) "Beverly76" wrote: I am trying to write a macro that duplicates a data evaluation procedure given to me from someone else. There are several steps requiring VLOOKUPs. I have been told that I cannot run a VLOOKUP within a macro? Is this a true statement. If I can do it, how? -- Sincerely, Beverly76 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup in a Macro
If you mean Range.Find, then you can tell excel to look for an exact match or
ignore the case. There are parms for that in that method. And all newer versions of excel (xl97 and higher) can use either: application.worksheetfunction.vlookup(...) or application.vlookup(...) (I _think_ that .worksheetfunction was added with xl97. But it may have been added with xl95.) Joel wrote: VBA code will only do Exact matches using the VBA Find(). Bt you can call the lokup worksheet functions (lookup, v lookup,hlookup) from VBA. Excel 2003 : worksheetfunctions.vlookup(...) Excel 2007 : application.vlookup(...) "Beverly76" wrote: I am trying to write a macro that duplicates a data evaluation procedure given to me from someone else. There are several steps requiring VLOOKUPs. I have been told that I cannot run a VLOOKUP within a macro? Is this a true statement. If I can do it, how? -- Sincerely, Beverly76 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup in a Macro
Dim res as variant 'could be an error
dim myRng as range dim myVal as variant 'or long or string or ... with worksheets("sheet9999") set myrng = .range("a:e") '5 columns end with myVal = worksheets("sheet8888").range("x77").value res = application.vlookup(myval, myrng, 3, false) if iserror(res) then msgbox "Error, not found" else msgbox res end if Beverly76 wrote: I am trying to write a macro that duplicates a data evaluation procedure given to me from someone else. There are several steps requiring VLOOKUPs. I have been told that I cannot run a VLOOKUP within a macro? Is this a true statement. If I can do it, how? -- Sincerely, Beverly76 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Macro? | Excel Worksheet Functions | |||
VLOOKUP Macro? | Excel Worksheet Functions | |||
VLOOKUP WITH MACRO | Excel Programming | |||
Vlookup in a macro | Excel Programming | |||
Please help.. VLookup Macro | Excel Programming |