FormulaR1C1 Autofill With Range That Is Row Variable

[limp]

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):

    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.

   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.

 

[Andrew Poulsom]

Welcome to the Board.

Isn't it?

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]

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

ActiveCell.Resize(totalrows).FormulaR1C1 = _
    "=IF(R7C1=""Method 1"",RC[-1]-R[1]C[-1],R[1]C[-1]-RC[-1])"

 

[p45cal]

try (untested):

Selection.AutoFill Destination:=Range("C15:C" & totalrows), Type:=xlFillDefault

or perhaps in one shot, doing away with autofill:

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]

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.

 

[limp]

Welcome to the Board.

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

    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!

 

[Peter_SSs]

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>