VBA for Dynamic Start Range Autofill

sushi514

New Member
Joined
Nov 13, 2020
Messages
18
Hi All,

I'm seeking some help for applying the autofill for a dynamic range, but when the start of the range can be anywhere in the spreadsheet.

In my example below, I'm using:

Range("C" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = _
"=XLOOKUP(RC[-2],'Sheet1 (3)'!C[3],'Sheet1 (3)'!C[-2],2,0)"
Selection.AutoFill Destination:=Range("C" & Rows.Count).End(xlUp).Offset(1)

I know the line with the autofill destination range is incorrect and what I'm seeing as suggestions is only for when you're starting at the beginning of a sheet, so the suggestion is: Selection.AutoFill Destination:=Range("C2: C" & Rows.Count).End(xlUp).Offset(1)

But in my case the start of the range is C199, not C2, and can vary with every report run.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,565
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
With Range("C" & Rows.Count).End(xlUp).Offset(1)
   .Formula2R1C1 = _
      "=XLOOKUP(RC[-2],'Sheet1 (3)'!C[3],'Sheet1 (3)'!C[-2],2,0)"
   .AutoFill Range(.Item(1), Range("A" & Rows.Count).End(xlUp).Offset(, 2))
End With
 

sushi514

New Member
Joined
Nov 13, 2020
Messages
18
Thanks for the welcome!

Unfortunately it doesn't seem to like the ".Item(1)" reference.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,565
Office Version
  1. 365
Platform
  1. Windows
What error do you get?
 

sushi514

New Member
Joined
Nov 13, 2020
Messages
18

ADVERTISEMENT

It says "Compile error: Invalid or unqualified reference."

Also, I figured I'd upload a visual just in case, where Column C is where I'm trying to populate the data.
 

Attachments

  • Macro.png
    Macro.png
    7.6 KB · Views: 5

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,565
Office Version
  1. 365
Platform
  1. Windows
Did you retype the code, or copy/paste?
If the former do you have the full stop in front of Items?
 

sushi514

New Member
Joined
Nov 13, 2020
Messages
18

ADVERTISEMENT

I copied/pasted and came across the error message "Compile error: Invalid use of property" at the .Offset (1) section, actually.

So then I thought perhaps it was missing the ".Select" and "ActiveCell" pieces, added those in, and that is when I received the stop in front of Items.
 

Attachments

  • macrotest.png
    macrotest.png
    15.9 KB · Views: 3

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
I copied/pasted and came across the error message "Compile error: Invalid use of property" at the .Offset (1) section, actually.

So then I thought perhaps it was missing the ".Select" and "ActiveCell" pieces, added those in, and that is when I received the stop in front of Items.
Are you sure you copy/pasted? Apart from the Select & active cell that you mentioned changing, your image does not include some of the text included in Fluff's code

Rich (BB code):
With Range("C" & Rows.Count).End(xlUp).Offset(1)
   .Formula2R1C1 = _
      "=XLOOKUP(RC[-2],'Sheet1 (3)'!C[3],'Sheet1 (3)'!C[-2],2,0)"
   .AutoFill Range(.Item(1), Range("A" & Rows.Count).End(xlUp).Offset(, 2))
End With
Fluff's code as posted works for me.
 

sushi514

New Member
Joined
Nov 13, 2020
Messages
18
Thanks both, it worked! I wasn't sure if the "With" and "End With" commands were literal, or if they were just markers. I'm fairly new at macro writing so that wasn't apparent to me.

Do you know why I would need the "With" and "End With" in this case, though? All my other prompts just begin without them, like:

Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'Sheet1 (2)'!C[-3]:C[-2],2,0)"
Selection.AutoFill Destination:=Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
It is not so much that you need With ... End With but it is an efficient way to code.

Selecting ranges in code is almost never required and can slow your code considerably.
Using With ... End With saves selecting & therefore saves time. In this case it also saves repeating the range at the start of each relevant line within that block & apart from saving in repeating the range code it also saves another tiny amount of time since vba only has to establish the range once for the block instead of once per line in the block.
I would recommend that you try to learn how to avoid selecting in your code wherever possible.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,611
Messages
5,625,829
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top