VLOOKUP Fill Right for Multiple Rows and Dynamic Range of Columns

[FrenchCelt]

Hello,

I'm in the process of making a macro and I'm stuck at a section where I've done a VLOOKUP and filldown several rows, and the next part is to fill right all of those rows at once to an indeterminate number of columns to the right (my VLOOKUP formula is set up to adjust accordingly as it gets filled to the right down all the rows--it works fine if I do it manually). I know how to fill right for one row when the column number is dynamic, but doing it for rows B41:B54 (these are fixed and will always be the same starting point) to whichever column where the data ends has proven tricky.

This is what I have so far to get to where I'm stuck:

Range("B41").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,R3:R16,COLUMN(R[-40]C),FALSE)"
Selection.AutoFill Destination:=Range("B41:B54"), Type:=xlFillDefault

I have this set up earlier in my code to dynamically fill right something else:

lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("B40:B" & lastrow).AutoFill Destination:=Range(Cells(40, "B"), Cells(lastrow, lastcol)), Type:=xlFillDefault

So I wanted to try something similar with a range of B41:B54 to fill right, but nothing I tried has worked.

Does anyone have any suggestions?

 

[Fluff]

How about

lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("B41", Cells(lastrow, lastcol)).FormulaR1C1 = "=VLOOKUP(RC1,R3:R16,COLUMN(R[-40]C),FALSE)"

 

[FrenchCelt]

That didn't work. It filled to the right in Row 41 with this:

=VLOOKUP($A41,$3:$16,COLUMN(#REF!),FALSE) in B41

And wiped out the data I had in Row 40, replacing with this:

=VLOOKUP($A40,$3:$16,COLUMN(B1048576),FALSE) in B40

 

[Fluff]

That suggests that row 40 is the last row with data in col B.
If you want it on rows 41:54 regardless of last row, try

lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
Range("B41", Cells(54, lastcol)).FormulaR1C1 = "=VLOOKUP(RC1,R3:R16,COLUMN(R[-40]C),FALSE)"

 

[FrenchCelt]

Excellent. That did the trick. You're awesome as always.

 

[Fluff]

You're welcome & thanks for the feedback.