Vlookup

[Guest]

Good morning. I use the following function to fill each cell in column 20.

VLOOKUP(B2,MSC.xls!$A$2:$C$322,3,FALSE)


This works great, but I need to turn it into VBA code. The B2 would have to
increment for each cell (i.e. B2, B3, B4, B5... etc) until the last row was
completed.

Thanks Bill

 

[Guest]

Hi Bill
not tested but HTH

Sub VlookupFill()

Range("T2").FormulaR1C1 = "=VLOOKUP(RC2,MSC.xls!R2C1:R[320]C3,3,FALSE)"
Range("T2").AutoFill Destination:=Range("T2:T" & Range("B2:B10000").End
_(xlUp).Row)

End Sub

 

[Guest]

I tried it. It did not work for my sheet. Thanks for your help.

Hi Bill
not tested but HTH

Sub VlookupFill()

Range("T2").FormulaR1C1 = "=VLOOKUP(RC2,MSC.xls!R2C1:R[320]C3,3,FALSE)"
Range("T2").AutoFill Destination:=Range("T2:T" & Range("B2:B10000").End
_(xlUp).Row)

End Sub

--
Regards
Aussie Bob C.

Good morning. I use the following function to fill each cell in column 20.

VLOOKUP(B2,MSC.xls!$A$2:$C$322,3,FALSE)


This works great, but I need to turn it into VBA code. The B2 would have to
increment for each cell (i.e. B2, B3, B4, B5... etc) until the last row was
completed.

Thanks Bill

 

[Guest]

Good morning. I use the following function to fill each cell in column 20.

VLOOKUP(B2,MSC.xls!$A$2:$C$322,3,FALSE) 'The B2 would have to increment


This works great, but I need to turn it into VBA code. The B2 would have to
increment for each cell (i.e. B2, B3, B4, B5... etc) until the last row was
completed.

Thanks Bill

 

[Dave Peterson]

How do you know that last cell in column T--from looking at column A????

dim LastRow as long
with worksheets("sheet1")
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("t2:t" & lastrow).formula = "=vlookup(b2,msc.xls!$a$2:$c$322,3,false)"
end with

(First cell to get the formula is T2???)

Is Msc.xls a worksheet name?

If yes, then that's kind of weird. If no, then where's the worksheet name in
your formula?

I'd get that formula working manually, then copy it into my code.