Need help with VBA conditional VLOOKUP on another file

[jonnyjonjon]

Hello,

I'm very new to VBA programming and I am required to complete a certain task in my current work project with VBA. I've tried to program it myself but it does not seem to work. In short, I'm required to perform a VLOOKUP function on another excel file.

There are two files, called "FileX" and "FileY". FileX is the main file and FileY is where the data will be taken from.

The VLOOKUP function will only be done if the value in column N for that particular row is either "Approved" or "Quoted".
For the VLOOKUP function, it will be placed on column BH and the values are,
Lookup valve = A2 of FileX
Range containing lookup value = Columns R:BK of FileY
Column number = 21
False

Also, is it able for the marco to perform this for every single row, until an empty row is detected?

Would appreciate any help, and thank you so much in advance.

[Bob Phillips]

Off the top



Dim lastrow As Long
Dim i As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow

.Cells(i, "M").Formula = "=IF(OR(N2=""Approved"",N2=""Quoted""),VLOOKUP(A2,'[File Y]Sheet1'!R:BK,21,FALSE),"""")"
Next i
End With

[jonnyjonjon]

Off the top



Dim lastrow As Long
Dim i As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow

.Cells(i, "M").Formula = "=IF(OR(N2=""Approved"",N2=""Quoted""),VLOOKUP(A2,'[File Y]Sheet1'!R:BK,21,FALSE),"""")"
Next i
End With

Thank you for the response, but may I ask why is it " .Cells(i, "M").Formula" instead of " .Cells(i, "BH").Formula " ?

If I run the default one that you provided for me, nothing happens, but when I change it to BH instead of M, the values come out. But there is a problem, all the values are the same as the first one. Is it because all the other cells below the first one are referencing the same lookup value ? How do I undo that?

Thanks.

[Bob Phillips]

Dim lastrow As Long
Dim i As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(i, "BH").Resize(lastrow - 1).Formula = "=IF(OR(N2=""Approved"",N2=""Quoted""),VLOOKUP(A2,'[File Y]Sheet1'!R:BK,21,FALSE),"""")"
End With