VBA for Dynamic Start Range Autofill

sushi514

New Member
Joined
Nov 13, 2020
Messages
30
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
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: 11
Upvote 0
Did you retype the code, or copy/paste?
If the former do you have the full stop in front of Items?
 
Upvote 0
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: 6
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top