How to add via programming ?

  • Thread starter Thread starter Milind
  • Start date Start date
M

Milind

I need to open a file, count the occupied rows, and then total column C,
three rows below the last occupied row. I tried using the FormulaR1C1
option, but got confused on how to place the value of the variable, used to
count occupied rows.

Any ideas

Milind
 
Milind,

Dim cRows As Long

cRows = Cells(Rows.Count, "C").End(xlUp).row
Cells(cRows + 3, "C").FormulaR1C1 = "=SUM(R1C:R[-3]C)"


Haven't bothered with the file open bit, as I assume you already have that.
 
One way:

With Range("C" & Rows.Count).End(xlUp).Offset(3, 0)
.Formula = "=SUM(C1:C" & .Row - 3 & ")"
End With

or

With Range("C" & Rows.Count).End(xlUp).Offset(3, 0)
.FormulaR1C1 = "=SUM(R1C:R[-3]C)"
End With
 
Milind,

Dim lrow As Long
' lrow = last used row in column C

lrow = Cells(Rows.COUNT, "C").End(xlUp).Row

Cells(lrow +3,3).FormulaR1C1 = "=Sum(R1C3:R" & lrow & "C3)"

or

Range("C" & lrow + 3).Formula = "Sum(C1:C" & lrow & ")"
 
Back
Top