IF-THEN AUTOSUM Macro

[kharris]

I am trying to create a macro that autosums columns with a varying number of rows without including numbers from the sets of data above it. Her is an example:

Row 1
Row 2
Row 3
Autosum Rows 1-3

Row 4
Row 5
Autosum Rows 4-5

I found this formula that seems to work for the most part:

ActiveCell.Formula = "=SUM(" & Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)).Address & ")"

It works great for the most part. It just doesn't work if I need to just get the sum for only one row of data. It creates a sum for the one row to the sum of the rows above it. I thought about doing an if-then statement that checks the row 2 rows above the autosum to see if it is empty. If it is then it will just equal the row above the autosum. If not, it will run my autosum formula.

I've tried this formula and it works:

ActiveCell.FormulaR1C1 = "=IF(R[-2]C=0,R[-1]C,SUM(R[-1]C:R[-2]C))"

And my autosum formula works, so why doesn't this formula work?

ActiveCell.Formula = "=IF(R[-2]C=0,R[-1]C,SUM(" & Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)).Address & "))"

Please let me know if you have any suggestions on how to make any of these formulas work. Any help is appreciated. I am new to all of this.

 

[AlphaFrog]

The default reference style for the .Address property is A1 and not R1C1. You cannot create a formula with both A1 and R1C1 reference styles in it.

Try this...

ActiveCell.Formula = "=IF(R[-2]C=0,R[-1]C,SUM(" & Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)).Address[COLOR=#FF0000](ReferenceStyle:=xlR1C1)[/COLOR] & "))"

This code may do what you want as well. Change the column to suit.

    [COLOR=darkblue]Dim[/COLOR] a [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] a [COLOR=darkblue]In[/COLOR] Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants, xlNumbers).Areas
        a(a.Count + 1).Formula = "=SUM(" & a.Address & ")"
    [COLOR=darkblue]Next[/COLOR] a