How to construct VLOOKUP in VBA?

[mogens.kjaer]

You should supply your code or workbook as you probably want a CASE statement, however you asked for VLOOKUP so...[vba]Dim MyVar As String
Dim MyVarResult
MyVarResult = Application.WorksheetFunction.VLookup(MyVar, Sheets("Sheet2").Range("A1:A249"), 2, False)
If MyVarResult <> "" Then
'DO SOMETHING
Else
'DO SOMETHING ELSE
End If[/vba]MyVar would be your variable. Take a look at Case in conjunction with the worksheet selection_change event.

Hi Simon,

It seems that you have some experience with VLookup in VBA, which I have not.

I tried to include the following statements in a Sub:

Dim Konti as Range
Set Konti = Range("Konto_Col")
KontoCol = Application.WorksheetFunction.VLookup(Kontonr, Konti, 2, False)

"Konto_Col" is a 2 column range in the worksheet. "Kontonr" is passed to the sub as an argument.

When running the sub I get the following error message (translation Danish/English might not be exact):

Run time error '1004': Cannot use the property VLookup for the class WorksheetFunction.

Can you or somebody else tell, what is wrong?

Best regards