Autofill Dynamic Selection Range

[JayR33]

Hi all,

I currently use the below code to select the first empty cell on row 2 and then do a vlookup formula and finally auto fill that formula to the bottom of the data (I have not pasted the vlookup line as it is irrelevant).

Cells(2, Range("A2").End(xlToRight).Column + 1).Select - This selects the first empty cell on row 2

*VLOOKUP FORMULA*

Selection.AutoFill Destination:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select - This auto fills to the bottom of the data for the cell selected in step 1. However I can no longer use this as I need the range to be dynamic.

My problem is that the second line of code states cells "B2:B" but this won't always be the same range. I need this range to be the same as the one selected during the first line of code.

Is there anything I can add to the auto fill code, to always select the same range as the one selected in step 1, rather than always selecting column B (which was the first set of data ran)?

Thanks in advance.

 

[Joe4]

Welcome to the board!

You can set any range equal to a range variable. Then, you can simply reference that range variable anywhere in your code without having to recalulcate anything, i.e.

Dim c as Long
Dim r as Long
Dim rng as Range

'Find column to apply formulas to
c = Cells(2, Range("A2").End(xlToRight).Column + 1

'Find last row to appy formula to:
r = Range("A" & Rows.Count).End(xlUp).Row

'Set range to apply formulas to
Set rng = Range(Cells(2, c), Cells(r, c))

'Set VLOOKUP formula for whole range
rng.Formula = ...

 

[JayR33]

Welcome to the board!

You can set any range equal to a range variable. Then, you can simply reference that range variable anywhere in your code without having to recalulcate anything, i.e.

Dim c as Long
Dim r as Long
Dim rng as Range

'Find column to apply formulas to
c = Cells(2, Range("A2").End(xlToRight).Column + 1

'Find last row to appy formula to:
r = Range("A" & Rows.Count).End(xlUp).Row

'Set range to apply formulas to
Set rng = Range(Cells(2, c), Cells(r, c))

'Set VLOOKUP formula for whole range
rng.Formula = ...

Thanks for the reply!

So in terms of my code I have the below:

1. Cells(2, Range("A2").End(xlToRight).Column + 1).Select
2. ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],'Ref Sheet'!C[-1]:C[3],5,0),0)"
3. Cells(2, Range("A2").End(xlToRight).Column).Select
4. Selection.AutoFill Destination:=Cells(2, LastColumn), Type:=xlFillFormats

1. Selects the last column in row 2 with data
2. Does the vlookup formula that I need
3. Selects the same column as step 1 (this time it contains data)
4. Auto fills the data in the recently populated cell

Step 4 is the one I am having the issue with. I need dynamic ranges on the destination for the auto fill. So each time I run this macro it will be populating the next column. I already have info in column B, so the next time I need it to do column C, then column D etc...

I need step 4 to find the last column with data in row 2 and auto fill it down to the end of the data.

 

[Joe4]

Did you look at what I did?
I find the next column you need to populate, the last row you need to populate, and then set that whole range at once.
Then I apply the formula to the entire range at once (there is no to set the formula to the first cell and then use AutoFill for the rest of the cells in that column - you can apply the formula to the WHOLE range at once WITHOUT using AutoFill).

 

[JayR33]

Did you look at what I did?
I find the next column you need to populate, the last row you need to populate, and then set that whole range at once.
Then I apply the formula to the entire range at once (there is no to set the formula to the first cell and then use AutoFill for the rest of the cells in that column - you can apply the formula to the WHOLE range at once WITHOUT using AutoFill).

Where would that go in my code? I'm pretty new to all of this so don't fully understand where to copy and paste stuff etc.

 

[Joe4]

Where would that go in my code? I'm pretty new to all of this so don't fully understand where to copy and paste stuff etc.

It would simply replace all the code you showed in your original post.
You just need to fill-in the VLOOKUP formula, i.e.

Dim c as Long
Dim r as Long
Dim rng as Range

'Find column to apply formulas to
c = Cells(2, Range("A2").End(xlToRight).Column + 1)

'Find last row to appy formula to:
r = Range("A" & Rows.Count).End(xlUp).Row

'Set range to apply formulas to
Set rng = Range(Cells(2, c), Cells(r, c))

'Set VLOOKUP formula for whole range
rng.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'Ref Sheet'!C[-1]:C[3],5,0),0)"

 

[JayR33]

It would simply replace all the code you showed in your original post.
You just need to fill-in the VLOOKUP formula, i.e.

Dim c as Long
Dim r as Long
Dim rng as Range

'Find column to apply formulas to
c = Cells(2, Range("A2").End(xlToRight).Column + 1

'Find last row to appy formula to:
r = Range("A" & Rows.Count).End(xlUp).Row

'Set range to apply formulas to
Set rng = Range(Cells(2, c), Cells(r, c))

'Set VLOOKUP formula for whole range
rng.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'Ref Sheet'!C[-1]:C[3],5,0),0)"

I have copied and pasted and get the below error, so I must be doing something wrong, apologies:

 

[Joe4]

Looks like you actually tried typing it yourself instead of using Copy/Paste.
What happened to the word "Set" at the beginning of that line?

 

[JayR33]

Looks like you actually tried typing it yourself instead of using Copy/Paste.
What happened to the word "Set" at the beginning of that line?

Apologies, that was after it didn't work and I was removing it.

I have tried again with the below code and got the below errors (still an error with that line):

 

[Joe4]

Hover over "c" and "r" when you get that error, amnd tell me what their values are.