VLookup VBA Code Fault...

[sarismus]

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.

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

 

[venkat1926]

the data will be like this in sheet1

Excel Workbook
	F	G	H	I	J	K	L	M
1
2
3							english
4							french
5
6		english	french
7	1005	apple	pomme
8	1007	banana	banae
9	1009	lemon	citron
10	1008	melon	melon d"eau
11	1006	corn	mais
Sheet1

NOTE: THE ENTRIES IN g6 AND h6. THIS WILL EASE WRITING MACRO(event code)

right click the tab of sheet1 and click view code. in the window that come up copy this event code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cfind As Range, r As Range, r1 As Range
Worksheets("sheet2").Cells.Clear
If Target.Address <> "$L$3" And Target.Address <> "$L$4" Then GoTo enableevents
Application.enableevents = False
On Error GoTo enableevents
'Set cfind = Columns("F:H").Cells.Find(what:=Target, lookat:=xlWhole, after:=Target.Address)
Set cfind = Cells.Find(what:=Target, lookat:=xlWhole, searchorder:=xlRows, after:=Target)


If Not cfind Is Nothing Then
Set r = Range(Cells(cfind.Row + 1, "F"), Cells(cfind.Row + 1, "F").End(xlDown))
'MsgBox r.Address
r.Copy
With Worksheets("sheet2")
.Range("A1").PasteSpecial
End With
Set r1 = Range(Cells(cfind.Row + 1, cfind.Column), Cells(cfind.Row + 1, cfind.Column).End(xlDown))
'MsgBox r1.Address
r1.Copy
With Worksheets("sheet2")
.Range("B1").PasteSpecial
End With
End If


MsgBox "event code over. see sheet2"
enableevents:
 Application.enableevents = True
End Sub

save the file and close and then open the file and enable macros

select L3 or L4 automatically the macro will be run and result will be in sheet2

feedback please

 

[sarismus]

First of all,

Thanks for answer and help.

Firstly I tried your codes step by step what you wrote.But It didn't work or I didn't work it.

I think it's my fault I didn't describe my problem.

I wanted assign this formula ----->=IF

(L2=1,VLOOKUP(A1,$F$7:$H$11,2,0),VLOOKUP(A1,$F$7:$H$11,3,0)) to my combobox.

That's all folks.

Sorry for my english I'm improving.

Thanks again for your help.

 

[venkat1926]

"But It didn't work or I didn't work it."
not clear. vague

in sheet 1 if you selecst L3(if already selected select L2 and again bring selection to L3. yoour sheet 2 will be like this . is this not what you want?

Sheet2

*	A	B
1	1005	apple
2	1007	banana
3	1009	lemon
4	1008	melon
5	1006	corn

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


of course this is not formula but vba code(event happens when you select l3 or L4 in sheet1

where did you park the event code. It should not be parked in module. I have told you where to park.

 

[sarismus]

"But It didn't work or I didn't work it."
not clear. vague

in sheet 1 if you selecst L3(if already selected select L2 and again bring selection to L3. yoour sheet 2 will be like this . is this not what you want?

Sheet2

*	A	B
1	1005	apple
2	1007	banana
3	1009	lemon
4	1008	melon
5	1006	corn

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


of course this is not formula but vba code(event happens when you select l3 or L4 in sheet1

where did you park the event code. It should not be parked in module. I have told you where to park.

Sorry for [FONT=Segoe UI, Verdana, Tahoma, Arial, Helvetica, sans-serif]to fail to understand.
[/FONT]
I thought that you assigned the code to combobox but it's not.

when I select the cell L3 or L4 Shett 2 is changing what I want.

I tried and it's worked your code.It's different point of view and it's serve to purpose.

Thanks in deed for your help and your patient.