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.

 

[JayR33]

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

C= 0
R= 140779

 

[Joe4]

C= 0
R= 140779

Try changing the line that calculates "c" to this:

c = Cells(2, Range("A2").End(xlToRight).Column + 1).Column

 

[JayR33]

Try changing the line that calculates "c" to this:

c = Cells(2, Range("A2").End(xlToRight).Column + 1).Column

Great, that works now! However I've noticed something else

My vlookup formula will also need to be dynamic won't it? Because at the moment it is filling in column B for example, but next time it will be filling in column C, and then column D next time etc. So if it's moving across 1 column each time, then my vlookup will be wrong as the reference point will be "RC-1" at the moment but next time it will need to be "RC-2".

 

[Joe4]

My vlookup formula will also need to be dynamic won't it? Because at the moment it is filling in column B for example, but next time it will be filling in column C, and then column D next time etc. So if it's moving across 1 column each time, then my vlookup will be wrong as the reference point will be "RC-1" at the moment but next time it will need to be "RC-2".

Then you just need to change your range reference from RELATIVE to ABSOLUTE, i.e.
change this:

RC[-1]

which says to choose one column to the left of wherever you are
to this:

RC1

which says pick the first column (column A).

 

[JayR33]

Then you just need to change your range reference from RELATIVE to ABSOLUTE, i.e.
change this:

RC[-1]

which says to choose one column to the left of wherever you are
to this:

RC1

which says pick the first column (column A).

Thanks a lot for the help Joe! Really appreciate it.

 

[Joe4]

You are welcome.
Glad I was able to help.