VLookup in a Macro

[Beverly76]

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?

 

[Per Jessen]

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

 

[Joel]

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(...)

 

[Dave Peterson]

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.)

 

[Dave Peterson]

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