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

 

[My Aswer Is This]

I would like to see you explain what your trying to do instead of showing us your code that is not working and then you say quote:

"if any expert help me fixing this code "

Tell us what your criteria is.

 

[shahamat]

I would like to see you explain what your trying to do instead of showing us your code that is not working and then you say quote:

"if any expert help me fixing this code "

Tell us what your criteria is.

"I am trying to compare two columns i.e. Column A in both sheets and column D for Letter "D"

 

[ask2tsp]

when I run the (slightly modified) code the result is
Sheet1

	A	B
A	Toyota
B
C	Honda
D
E

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:69.33px;"><col style="width:69.33px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

</tbody>

What are the letters in column A and column D?
What kind of result did you want?

More in general: can you describe in english, not in excel or vba code, what you want for a result from some other result.

 

[shahamat]

Thank you very much for your reply,
Column A in sheet1 is being compared with Column A of sheet2. Column A in sheet2 has duplicate values/letters, therefore the value of column A is conditional with Column D’s value of the same sheet.
In your results, D & E are blank, however D should have “Suzuki” and E should have “Nissan” because their column D has a value of D for both entries.

 

[ask2tsp]

Board member My answer is this and I asked you to describe what you want to do in a functional way.
You keep repeating things like "compare column A in both sheets". Is that a correct comparison? We don't know because you have to tell what is the meaning of the letters in colomn A.
Same for column D.
Also replying D should have “Suzuki” and E should have “Nissan” because their column D has a value of D for both entries is not very helpfull. It only says that your code is not correct.

So try to describe what has to be done without showing code.

 

[shahamat]

Board member My answer is this and I asked you to describe what you want to do in a functional way.
You keep repeating things like "compare column A in both sheets". Is that a correct comparison? We don't know because you have to tell what is the meaning of the letters in colomn A.
Same for column D.
Also replying D should have “Suzuki” and E should have “Nissan” because their column D has a value of D for both entries is not very helpfull. It only says that your code is not correct.

So try to describe what has to be done without showing code.

4510346

Dear Board Member,
Let me explain further and try to copy some more data for better understanding. My only requirement is to return column 2 from sheet2 on sheet1. Since there are duplicate values in column 1 of sheet2, therefore I want to have a condition that if column 4 on sheet2 has a code of AB, then return the value otherwise leave it blank.

[TABLE="width: 360"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Data on Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cost Code[/TD]
[TD]Make[/TD]
[TD]Model[/TD]
[TD]Sales Code[/TD]
[/TR]
[TR]
[TD]C001[/TD]
[TD]Toyota[/TD]
[TD]Corrola[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]C002[/TD]
[TD]Honda[/TD]
[TD]Accord[/TD]
[TD]CD[/TD]
[/TR]
[TR]
[TD]C003[/TD]
[TD]Ford[/TD]
[TD]Escape[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]C004[/TD]
[TD]Mazda[/TD]
[TD]6[/TD]
[TD]CD[/TD]
[/TR]
[TR]
[TD]C004[/TD]
[TD]Dodge[/TD]
[TD]Caravan[/TD]
[TD]CF[/TD]
[/TR]
[TR]
[TD]C004[/TD]
[TD]Fiat[/TD]
[TD]Tipo[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]C005[/TD]
[TD]Pugeot[/TD]
[TD]606[/TD]
[TD]CD[/TD]
[/TR]
[TR]
[TD]C005[/TD]
[TD]Rino[/TD]
[TD]ABCD[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]C006[/TD]
[TD]Saab[/TD]
[TD]XYZA[/TD]
[TD]CD[/TD]
[/TR]
[TR]
[TD]C006[/TD]
[TD]Audy[/TD]
[TD]Q7[/TD]
[TD]CF[/TD]
[/TR]
[TR]
[TD]C006[/TD]
[TD]Mercedez[/TD]
[TD]Benz[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]C007[/TD]
[TD]Nissan[/TD]
[TD]Xtrail[/TD]
[TD]CD[/TD]
[/TR]
[TR]
[TD]C007[/TD]
[TD]Mitsubishi[/TD]
[TD]Lancer[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]C008[/TD]
[TD]Suzuki[/TD]
[TD]Swift[/TD]
[TD]CD[/TD]
[/TR]
[TR]
[TD]C009[/TD]
[TD]Honda[/TD]
[TD]Civic[/TD]
[TD]AB
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 161"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Desired Results on Sheet1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Cost Code[/TD]
[TD]Make[/TD]
[/TR]
[TR]
[TD]C001[/TD]
[TD]Toyota[/TD]
[/TR]
[TR]
[TD]C002[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]C003[/TD]
[TD]Ford[/TD]
[/TR]
[TR]
[TD]C004[/TD]
[TD]Fiat[/TD]
[/TR]
[TR]
[TD]C005[/TD]
[TD]Rino[/TD]
[/TR]
[TR]
[TD]C006[/TD]
[TD]Mercedez[/TD]
[/TR]
[TR]
[TD]C007[/TD]
[TD]Mitsubishi[/TD]
[/TR]
[TR]
[TD]C008[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]C009[/TD]
[TD]Honda[/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

Try this:
I'm assuming Sheet (1) column (A) is already filled in.

Sub Toyota()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Sheets(1).Activate
    For i = 2 To Lastrow
        If Sheets(2).Cells(i, 4).Value = "AB" Then
            Sheets(2).Range("B" & i).Copy Destination:=Sheets(1).Range("B" & i)
        End If

    Next
Application.ScreenUpdating = True
End Sub

 

[shahamat]

Dear Group member, Appreciate your help in response to my query and sorry to bother you, after copying your code, I am not getting the desired result, it is skipping rows, especially where there is a duplicate value in the first column. I am copying first the table of desired result and secondly what i am getting from the code.
This is a desired result table,
[TABLE="width: 180"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Cost Code[/TD]
[TD]Make[/TD]
[/TR]
[TR]
[TD]C001[/TD]
[TD]Toyota[/TD]
[/TR]
[TR]
[TD]C002[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]C003[/TD]
[TD]Ford[/TD]
[/TR]
[TR]
[TD]C004[/TD]
[TD]Fiat[/TD]
[/TR]
[TR]
[TD]C005[/TD]
[TD]Rino[/TD]
[/TR]
[TR]
[TD]C006[/TD]
[TD]Mercedez[/TD]
[/TR]
[TR]
[TD]C007[/TD]
[TD]Mitsubishi[/TD]
[/TR]
[TR]
[TD]C008[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]C009[/TD]
[TD]Honda[/TD]
[/TR]
</tbody>[/TABLE]

Here is what I am getting from the code,
[TABLE="width: 189"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Cost Code[/TD]
[TD]Make[/TD]
[/TR]
[TR]
[TD]C001[/TD]
[TD]Toyota[/TD]
[/TR]
[TR]
[TD]C002[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]C003[/TD]
[TD]Ford[/TD]
[/TR]
[TR]
[TD]C004[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]C005[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]C006[/TD]
[TD]Fiat[/TD]
[/TR]
[TR]
[TD]C007[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]C008[/TD]
[TD]Rino[/TD]
[/TR]
[TR]
[TD]C009[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

My code looks in column (4) of sheet (2) for the value ("AB")

If the value is found then the value in Column ("B") of Sheet(2) is copied to the same row in Sheet(1) column "B""