PDA

View Full Version : How to construct VLOOKUP in VBA?



mogens.kjaer
05-16-2009, 01:29 PM
You should supply your code or workbook as you probably want a CASE statement, however you asked for VLOOKUP so...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 IfMyVar 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
05-16-2009, 02:56 PM
This works for me

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

mogens.kjaer
05-16-2009, 05:11 PM
This works for me

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

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

mdmackillop
05-16-2009, 05:23 PM
Option Explicit forces you to declare variables, but doesn't change the functionality.

Simon Lloyd
05-17-2009, 02:19 AM
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 :)