Please Help Me For VLOOKUP Function

[sarismus]

I am working on a project to create a simple VLOOKUP.

I have created the macro but it not works well, but now I am looking for some advise on how to modify the code.

I have a list of products(english and other names) with account codes.

I want to use VLOOKUP to find the English or French when I chose from combobox.

Combobox input range is $L$3:$L$4 and Cell Link $L$2

L3=English L4=French

L7:H11 is data range contains French and English accounts name with account codes.

For example;

F7=1005 G7=Apple H7=Pomme
F8=1007 G7=Banana H8=Banane
F9=1009 G7=Lemon H9=Citron
F10=1008 G7=Melon H10=Melon d'eau
F11=1006 G7=Corn H11=Maïs

İf A1=1005 then B1 cell must equal to Apple
A2=1007 B2 cell must equal to Banana
A3=1009 B3 cell must equal to Lemon
A4=1010 B4 cell must equal to N/A no answer
A5=1006 B5 cell must equal to Corn

Sub DropDown1_Change()
If [L2] = 1 Then
Dim Alan As Range
For Each Alan In Sheets(1).Range("A1:A5")
On Error GoTo Hata
Alan.Offset(0, 1) = WorksheetFunction.VLookup(Alan, Sheets(1).Range("F7:H11"), 2, False)
Hata: If Err Then MsgBox Alan.Address & "account not defined "
Next Alan
Else
For Each Alan In Sheets(1).Range("A1:A5")
On Error GoTo Hata
Alan.Offset(0, 1) = WorksheetFunction.VLookup(Alan, Sheets(1).Range("F7:H11"), 3, False)
Hata: If Err Then MsgBox Alan.Address & "account not defined "
Next Alan
End If
End Sub

 

[patel45]

MrExcel Message Board FAQ

it's the second time you start with help