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
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