VLOOKUP???

Posted by HELP on August 31, 2001 10:20 AM

Can someone tell me how to use the VLOOKUP formula in a macro? Not with the ActiveCell formula but with Cells.("., .") formula. Can someone help me with this? It's supposed to search for "sheet1" cell "F10" and it's supposed to look for it in "sheet2" column "A:H" and it's supposed to select the found cell from column 2 and it's "False". Hopefully somebody can help me and respond right away. Thanks!

HELP

Posted by Dax on September 01, 2001 6:50 AM

Hello,

To carry out your specific example you can use this code:-

Sub VLookup()
Dim rngeSource As Range
Dim sLookupValue As Variant

Set rngeSource = Sheets("Sheet2").Range("A:H")
sLookupValue = Sheets("Sheet1").Range("F10")
MsgBox Application.WorksheetFunction.VLookup(sLookupValue, rngeSource, 2, False)
End Sub

It's not too tricky to do this for other ranges and sheets once you understand the syntax.

HTH,
Dax.

Posted by HELP on September 01, 2001 2:11 PM

Dax,

Thanks for your help but do you also know how I can let it show the answer of the VLookup in "sheet3" cell "C4"? Thanks already!

HELP

Posted by Dax on September 02, 2001 10:09 AM

Sub VLookup()
Dim rngeSource As Range
Dim sLookupValue As Variant

Set rngeSource = Sheets("Sheet2").Range("A:H")
sLookupValue = Sheets("Sheet1").Range("F10")
Sheets("Sheet3").Range("C4")=Application.WorksheetFunction.VLookup(sLookupValue, rngeSource, 2, False)
End Sub