CODEWITHSUNDEEP
vbaexcel
Jenny
Jenny

Reputation: 451

using vlookup with if statement

I am having two sheets, sheet 1 as "BW" and sheet2 as "EP".

I am comparing the ID in my sheet1 (Column L) with the ID in my sheet2. If the ID matches, then I am copying the date of column G of sheet 2 to my sheet1 in column AA.

I have the code, working till this.

I would like to have an IF statement here.

If the Column E of my sheet2 has G70 confirmed, then I look for the ID and copy the dates of sheet2 to sheet1.

I tried the following IF statement and got the

If Cells(i, 5).Value = "G70 Confirmed" Then

Application defined error.

Can anyone help me to get rid of this and satisfy the requirement 

Sub lookup()
Dim TotalRows As Long, totalrowsSht2 As Long
Dim ws As Worksheet
TotalRows = Sheets("BW").Cells(Rows.Count, "A").End(xlUp).Row
totalrowsSht2 = Sheets("BW").Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, 5).Value = "G70 Confirmed" Then
Sheets("BW_PSW").Range("AA5:AA" & TotalRows).Formula = Application.WorksheetFunction.IfError(Application.VLookup(Sheets("BW").Range("L5:L" & totalrowsSht2), Sheets("eP").Range("$A:$L"), 7, 0), "")
End If
End Sub

Upvotes: 0

Views: 84

Answers (2)

user8285860
user8285860

Reputation:

Your code is confusing:

You need to clarify which worksheets "EP", "BW" and "BW_PSW" are.

Is this what your trying to accomplish...

For each row from the fifth row to the last row.

If the value of worksheet 2, column "E" is "G70 Confirmed", and the value in worksheet 1, column "L" is equal to the value in worksheet 2 column (whatever column "ID" is in).

Then

Copy the date from worksheet 2 column "G" to worksheet 1 column "AA"

If this is correct you need a For loop with an If/And statement.

Upvotes: 0

Mitch
Mitch

Reputation: 583

I see you have the following in your code:

TotalRows = Sheets("BW").Cells(Rows.Count, "A").End(xlUp).Row
totalrowsSht2 = Sheets("BW").Cells(Rows.Count, "A").End(xlUp).Row

Shouldn't the total rows of sheet 2 be "EP" instead of "BW" ?

totalrowsSht2 = Sheets("EP").Cells(Rows.Count, "A").End(xlUp).Row

I would probably use "Find" instead of vlookup, and cycle through both sheets, but that's just my preference.

Upvotes: 1

Related Questions