Excel VBA If statement with multiple conditions (Can I use Vlookup here?)

[shahamat]

Hello Excel experts,
I am new to excel VBA, I have a query using multiple IF statement and copying the result on another sheet, I tried Vlookup also, however nothing worked because there are multiple columns in 2nd sheet with the same name and different ColumnD, it looks code checks the first matching and then jumps to another one. I am copying some data here for better understanding. if any expert help me fixing this code I would really appreciate, Thanks in advance for your help!
Sheet 1, where I want to copy the required data based on certain conditions,
ColumnA ColumnB
A
B
C
D
E
Sheet 2, where the data is residing
ColumnA ColumnB ColumnC ColumnD
A Toyota Corrola D
B Mitsubishi Lancer B
C Honda Accord D
M Honda City D
Blank Cell
D Dodge Caravan A
D Toyota Camry B
D Suzuki Swift D
E Nissan XYZ D
My code is as follows,
Private Sub CommandButton1_Click()
Dim i As Long
Dim Lastrow As Long
Set Rng = Sheets("sheet2").Range("A1:D50")
Lastrow = 20

For i = 2 To lastrow
On Error Resume Next
If Sheets("sheet1").Cells(i, 1) <> "" And Sheets("sheet2").Cells(i, 1) <> "" Then

If Sheets("sheet2").Cells(i, 4).Value = "D" Then
If Sheets("sheet1").Cells(i, 1).Value = Sheets("sheet2").Cells(i, 1).Value Then

Sheets("sheet1").Cells(i, 2) = Sheets("sheet2").Cells(i, 2).Value
End If
End If
End If

Next i
End Sub

 

[shahamat]

Dear Group member,
Issue here is I have to compare amongst a range of cost codes (column1 of sheet2), if there are more values of same/similar cost code, the only thing which helps selection is the sales code (4^th Column).
Being new to Excel VBA, and in my layman terms, what I see here is, the cost code from sheeet1 gets selected for comparison in sheet2, if the conditions fails, (whether it doesn’t find comparison in the 1^st column of sheet2 or it doesn’t get sales code “AB”), it goes back to the sheet1 and selects the next cost code for search. Whereas it has to search from a range of similar cost codes in sheet2 and make the selection on the basis of 4^th column i.e. “AB”.

 

[shahamat]

one more thing, if it finds "AB" in the first line it gets selected, otherwise it goes back to sheet1.

 

[My Aswer Is This]

I'm lost.

Maybe someone else here at Mr. Excel will understand.

 

[shahamat]

Please don't give up,
If you see my initial code, you can understand from it. Check the IF statement, you can easily understand. However I don't know how to write/lookup a value in a range of values. Simply what I want is to lookup a value from sheet1 into sheet2 (both sheets Column A to be compared) and in sheet2 another condition that 4th column also needs to be checked i.e. in this case is "AB".

 

[shahamat]

I think this code is close, however another Loop needs to run in sheet 2 before it goest back to sheet 1 for the next row.