0
ActiveCell.Offset(1, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[" & End(xlUp).Row & "]C:R[-2]C)"

I'm attempting to sum a range which will vary in length, and starts 2 rows above where I'm inserting this formula.

I'm getting a "Compile Error:Syntax Error" message when i attempt to run.

Community
  • 1
  • 1
Jon Quigley
  • 3
  • 3
  • I'm going to guess that since you're setting the value of `ActiveCell`, and moving the cursor _while_ doing it, you're going to get inconsistent results, at best. **However**, that's just a guess since you haven't told us what's wrong - is this just some kind of test to see if "we" know what we're talking about? If you're getting a specific error, edit your post to include it, please. – FreeMan Jul 15 '15 at 13:54

1 Answers1

0

First, you are trying to use the Range.End property without telling .End what it is the end of.

With ActiveCell
    .FormulaR1C1 = "=SUM(R[" & .Parent.Cells(Rows.Count, .Column).End(xlUp).Row & "]C:R[-2]C)"
End With

Secondly, that formula is almost always going to be a circular reference since you want to total from two rows above the active cell (in the same column) to the last populated row in the same column. That sounds like it will include the cell itself which is a circular reference.

To sum from the second row (in the active cell's column) to two rows above the active cell then this would be appropriate.

With ActiveCell
    if .row > 3 then
        .FormulaR1C1 = "=SUM(R2C:R" & .row - 2 & "C)"
    end if
End With

If you needed this to start at the first row then the formula would begin with "=SUM(R1C:R" & ... .

  • This helps, but appears to add the total rows in that column to the anchor row number. That means it is creating a large range and referencing that in the formula, whereas i want to look up the contiguous block and select that as a range. – Jon Quigley Jul 15 '15 at 14:59
  • I think you'd be better served by A) identifying your rows before you attempt to set the formula, then create the formula with those specific values, and B) [avoid using `ActiveCell`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). – FreeMan Jul 15 '15 at 15:12
  • defeats the whole purpose; the top cell in the column will be varying length from the last cell. – Jon Quigley Jul 15 '15 at 15:33
  • Jeeped, I actually want to sum from the top of the column down through two rows above the active cell. – Jon Quigley Jul 15 '15 at 16:05
  • @Jeeped - thanks! That works great on the first instance. I then have two empty rows, and another contiguous column of data, and need to repeat the process. Problem is, the above code now has a static reference to the second row, rather than a dynamic CTRL + UP type reference to select the top cell in this next range. This same pattern repeats for about 10 groups. Thoughts? – Jon Quigley Jul 15 '15 at 16:42
  • @Jeeped... ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R" & Selection.End(xlUp).End(xlUp).Row & "C)" Needed that double End(xlUp) to push pass the bottom cell and up into the end of the range. Appreciate your help – Jon Quigley Jul 15 '15 at 17:30