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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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
54,272
Office Version
  1. 365
Platform
  1. 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
14,761
Office Version
  1. 365
Platform
  1. 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
54,272
Office Version
  1. 365
Platform
  1. 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>
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,585
Messages
5,832,601
Members
430,144
Latest member
bruno92

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