VBA Vlookup Suggestions

[ItalianPlatinum]

Hello - to accomplish multiple vlookups I have the below code. It doesn't, well to me, seem very efficient in the VBA world. is there a better more precise way i should be accomplishing the below?

Sheets("MAIN").Range("I8").Formula = "=VLOOKUP(D8,'FT'!B:J,2,false)"
Sheets("MAIN").Range("J8").Formula = "=VLOOKUP(D8,'FT'!B:J,3,false)"
Sheets("MAIN").Range("K8").Formula = "=VLOOKUP(D8,'FT'!B:J,4,false)"
Sheets("MAIN").Range("L8").Formula = "=VLOOKUP(D8,'FT'!B:J,5,false)"
Sheets("MAIN").Range("M8").Formula = "=VLOOKUP(D8,'FT'!B:J,6,false)"
Sheets("MAIN").Range("N8").Formula = "=VLOOKUP(D8,'FT'!B:J,7,false)"
Sheets("MAIN").Range("O8").Formula = "=VLOOKUP(D8,'FT'!B:J,8,false)"
Sheets("MAIN").Range("P8").Formula = "=VLOOKUP(D8,'FT'!B:J,9,false)"
Sheets("MAIN").Range("Q8").Formula = "=G8-N8"

Sheets("MAIN").Range("I8:Q8").Select
Selection.AutoFill Destination:=Range("I8:Q" & Range("A" & rows.count).End(xlUp).row)
Range(Selection, Selection.End(xlDown)).Select

 

[JoeMo]

Untested, but I think you can replace the entire block you posted with this:

With Sheets("MAIN")
    .Range("I8").Formula = "=VLOOKUP(D8,'FT'!B:J,Column(B:B),false)"
    .Range("I8:P8").FillRight
    .Range("Q8").Formula = "=G8-N8"
End With

Note the hard-coded column number 2 in your formula for cell I8 is replaced by COLUMN(B:B).

 

[Norie]

Try this.

With Sheets("MAIN")
    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("I8:P" & LastRow).Formula = "=VLOOKUP($D8,'FT'!$B:$J,COLUMNS($I:J),0)"
    .Range("Q8:Q" & LastRow).Formula = "=G8-N8"
End With

 

[ItalianPlatinum]

Try this.

With Sheets("MAIN")
    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("I8:P" & LastRow).Formula = "=VLOOKUP($D8,'FT'!$B:$J,COLUMNS($I:J),0)"
    .Range("Q8:Q" & LastRow).Formula = "=G8-N8"
End With

getting a variable not defined on "last row"

 

[Norie]

Add this to your other declarations.

Dim LastRow As Long

 

[ItalianPlatinum]

Untested, but I think you can replace the entire block you posted with this:

With Sheets("MAIN")
    .Range("I8").Formula = "=VLOOKUP(D8,'FT'!B:J,Column(B:B),false)"
    .Range("I8:P8").FillRight
    .Range("Q8").Formula = "=G8-N8"
End With

Note the hard-coded column number 2 in your formula for cell I8 is replaced by COLUMN(B:B).

and i would still need to include this?

Sheets("MAIN").Range("I8:Q8").Select
Selection.AutoFill Destination:=Range("I8:Q" & Range("A" & rows.count).End(xlUp).row)
Range(Selection, Selection.End(xlDown)).Select

 

[JoeMo]

and i would still need to include this?

Sheets("MAIN").Range("I8:Q8").Select
Selection.AutoFill Destination:=Range("I8:Q" & Range("A" & rows.count).End(xlUp).row)
Range(Selection, Selection.End(xlDown)).Select

Yes, if you want to fill the formulas down below row 8.

 

[Norie]

Careful with those references when copying the formula across.

 

[ItalianPlatinum]

Add this to your other declarations.

Dim LastRow As Long

WORKS! Thank you!