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

[mdmackillop]

This works for me
[VBA]
Option Explicit

Sub Test()
Dim k
k = 5
MsgBox KontoCol(k)
End Sub

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

[/VBA]

[mogens.kjaer]

This works for me
[vba]
Option Explicit

Sub Test()
Dim k
k = 5
MsgBox KontoCol(k)
End Sub

Function KontoCol(Kontonr)
Dim Konti As Range
Set Konti = Range("Konto_Col")
KontoCol = Application.WorksheetFunction.VLookup(Kontonr, Konti, 2, False)
End Function
[/vba]

Thank you so much. It also works for me. It must be the "Option Explicit" that makes the difference.

[mdmackillop]

Option Explicit forces you to declare variables, but doesn't change the functionality.

[Simon Lloyd]

Mogens, i understand that your question was related to the thread you posted in but it really is best that you start your own (i have done this for you as these posts have been moved) as your problem may be unique