How to add via programming ?

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
 
B

Bob Phillips

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.
 
J

J.E. McGimpsey

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
 
S

steve

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 & ")"
 
Top