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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.514 / Virus Database: 312 - Release Date: 8/28/2003

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.

--

HTH

Bob Phillips

"Milind" wrote in message
...
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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.514 / Virus Database: 312 - Release Date: 8/28/2003

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


In article ,
"Milind" wrote:

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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.514 / Virus Database: 312 - Release Date: 8/28/2003

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

--
sb
"Milind" wrote in message
...
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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.514 / Virus Database: 312 - Release Date: 8/28/2003