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

IIRC, it's WorksheetFunction.VLOOKUP( )

"hverne" wrote:

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

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.

--
If this helps, please remember to click yes.


"hverne" wrote:

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

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.


hverne wrote:

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

--

Dave Peterson

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"hverne" wrote in message
...
How do I do the equivalent of the Excel VLOOKUP function in an Excel
(2007)
Macro?