Hi,
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 French 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 G8=Banana H8=Banane
F9=1009 G9=Lemon H9=Citron
F10=1008 G10=Melon H10=Melon d'eau
F11=1006 G11=Corn H11=Maïs
when I chosee English;
İ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
Thanks for your warning and helps.
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 French 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 G8=Banana H8=Banane
F9=1009 G9=Lemon H9=Citron
F10=1008 G10=Melon H10=Melon d'eau
F11=1006 G11=Corn H11=Maïs
when I chosee English;
İ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
Thanks for your warning and helps.
Code:
[COLOR=#333333]Sub DropDown1_Change()[/COLOR]
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
[COLOR=#333333]End Sub[/COLOR]
Last edited: