FormulaR1C1 Autofill With Range That Is Row Variable

limp

New Member
Joined
Aug 3, 2010
Messages
2
Hi all,

I am trying to autofill a range in which the Row number of the "end of range" cell (i.e. 25 in my first code) is specified by a variable.

I got my code working for a specified range (C15:C25):

Code:
    ActiveCell.FormulaR1C1 = _
        "=IF(R7C1=""Method 1"",RC[-1]-R[1]C[-1],R[1]C[-1]-RC[-1])"
    Range("C15").Select
    Selection.AutoFill Destination:=Range("C15:C25"), Type:=xlFillDefault
    Range("C15:C25").Select
Now, I want the row number of the range end to be specified according to the value of the variable totalrows.

I tried to do it like following, however, I get an "Autofill method of Range class failed" error.

Code:
   totalrows_int = 30
 
    ActiveCell.FormulaR1C1 = _
    "=IF(R7C1=""Method 1"",RC[-1]-R[1]C[-1],R[1]C[-1]-RC[-1])"
    Range("C15").Select
    Selection.AutoFill Destination:=Range(Cells(3, 15), Cells(3, totalrows)), Type:=xlFillDefault
    Range(Cells(3, 15), Cells(3, totalrows)).Select
Any help will be much appreciated.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to the Board.

Isn't it?

Code:
Selection.AutoFill Destination:=Range("C15:C" & totalrows), Type:=xlFillDefault
Note that destination must include the source range (ie the cell in which you have put your formula). Is that C15?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

You probably do not even need to select the starting cell. You rarely need to select anything to work with it in code and actually selecting it slows your code considerably. However, without knowing exactly what you are trying to do, see if this line helps
Code:
ActiveCell.Resize(totalrows).FormulaR1C1 = _
    "=IF(R7C1=""Method 1"",RC[-1]-R[1]C[-1],R[1]C[-1]-RC[-1])"
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
try (untested):

Code:
Selection.AutoFill Destination:=Range("C15:C" & totalrows), Type:=xlFillDefault
or perhaps in one shot, doing away with autofill:
Code:
Range("C15:C" & totalrows).FormulaR1C1 = "=IF(R7C1=""Method 1"",RC[-1]-R[1]C[-1],R[1]C[-1]-RC[-1])"
If the formula's not quite right it'll probably be fixable with a small adjustment to its relative/absolute refs.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,687
Office Version
2019
Platform
Windows

ADVERTISEMENT

Also the fact that totalrows has no set value isn't helping, your range is setting to "C15:C", which is causing the error. totalrows and totalrows_int are not the same.
 
Last edited:

limp

New Member
Joined
Aug 3, 2010
Messages
2
Welcome to the Board.
Peter_SSs said:
Welcome to the MrExcel board!
Thanks for your welcome Andrew and Peter_SSs.

I am happily impressed with all the responses!

I followed Andrew's solution and it worked for me. You guys are right that it's not needed to select the starting cell so I removed that part. I also added my formula into C15 before autofilling. The code that worked for me is the following

Code:
    Range("C15").FormulaR1C1 = "=IF(R7C1=""Method 1"",RC[-1]-R[1]C[-1],R[1]C[-1]-RC[-1])"
    Range("C15").Select
    Selection.AutoFill Destination:=Range("C15:C" & totalrows), Type:=xlFillDefault
    Range("C15:C" & totalrows).Select
Thanks a lot for your help guys!
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
You guys are right that it's not needed to select the starting cell so I removed that part.
Well, you really only removed a small part - you still have several 'Select' statements in your code.

If you really want to use AutoFill, you could use (without any 'Select'-ing):

<font face=Courier New>Range("C15").FormulaR1C1 = "=IF(R7C1=""Method 1"",RC[-1]-R[1]C[-1],R[1]C[-1]-RC[-1])"<br>Range("C15").AutoFill Destination:=Range("C15:C" & totalRows), Type:=xlFillDefault</FONT>



But I still point aout that you could do it all at once with one line:

<font face=Courier New>Range("C15:C" & totalRows).FormulaR1C1 = "=IF(R7C1=""Method 1"",RC[-1]-R[1]C[-1],R[1]C[-1]-RC[-1])"</FONT>
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,591
Messages
5,512,259
Members
408,886
Latest member
kashifziatevta423

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top