# VLOOKUP Fill Right for Multiple Rows and Dynamic Range of Columns

#### FrenchCelt

##### Board Regular
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:

VBA Code:
``````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:

VBA Code:
``````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?

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Fluff

##### MrExcel MVP, Moderator
VBA Code:
``````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

##### Board Regular
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

##### MrExcel MVP, Moderator
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
VBA Code:
``````lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
Range("B41", Cells(54, lastcol)).FormulaR1C1 = "=VLOOKUP(RC1,R3:R16,COLUMN(R[-40]C),FALSE)"``````

#### FrenchCelt

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

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
5
Views
83
Replies
1
Views
119
Replies
5
Views
82
Replies
6
Views
253
Replies
1
Views
85