What is the VBA equivalent of the excel function VLOOKUP

[hverne]

How do I do the equivalent of the Excel VLOOKUP function in an Excel (2007)
Macro?

 

[Barb Reinhardt]

IIRC, it's WorksheetFunction.VLOOKUP( )

 

[Paul C]

Application.WorksheetFunction.VLookup(lookup_value, table_array,
col_index_num, [range_lookup])

Be careful with the syntax if using named ranges as your table array.

This is an example from one of my programs:
Application.WorksheetFunction.VLookup(matl, Range("matprop"), 9, False)
"matprop" is a named range within the currently active workbook.
matl is a variable with VBA.

 

[Dave Peterson]

Dim myVal as Variant 'long, string, ???
Dim myRng as range
dim res as variant 'could return an error

with worksheets("Sheet2")
set myrng = .range("a:e") 'some range
end with

myval = worksheets("Sheet1").range("A1").value

res = application.vlookup(myval, myrng, 5, false)
if iserror(res) then
msgbox "No match" 'like #N/A in excel
else
msgbox res
end if

=========
There is a difference in the way application.vlookup() and
worksheetfunction.vlookup() (or application.worksheetfunction.vlookup()) behave.

Saved from a previous post:

Application.vlookup returns an error that you can check:

dim Res as variant 'could return an error
res = application.vlookup(....)
if iserror(res) then
msgbox "no match"
else
msgbox res
end if

Application.worksheetfunction.vlookup raises a trappable error that you have to
catch:

dim res as variant
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.number <> 0 then
msgbox "no match"
else
msgbox res
end if
on error goto 0

(application.match() and application.worksheetfunction.match() behave the same
way.)

Personally, I find using the application.vlookup() syntax easier to read. But
it's personal preference.

 

[Don Guillett]

Homework?

Sub VlookupVBA()
mv = "item2"
Set found = Columns(1).Find(mv, After:=Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not found Is Nothing Then
MsgBox found.Offset(, 1)
Else
MsgBox "Not found"
End If
End Sub