VBA syntax to SUM() at two cells below last data item

[Guest]

Using 2003 & 97

Need VBA syntax to SUM() at two cells below last data item.

In the code below, I selected the cell using relative then absolute
referencing

ActiveCell.Offset(-3, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-1]C)"

Seeing the above, I am not sure of the syntax for VBA to find the cell two
rows
below the last data item; summing from Row 2 to the last data item and place
an
Excel formula in that cell.

Thoughts?

TIA

Dennis

 

[Guest]

Clarification:

There are ten different worksheets that will have varying amounts of data
every month.

Therefore, the VBA code needs to place the SUM() at different spots.


***************************

 

[JE McGimpsey]

one way:

Cells(Rows.Count, ActiveCell.Column).End(xlUp)(3, 1).FormulaR1C1 = _
"=SUM(R2C:R[-2]C)"

 

[Guest]

Thanks! ..... one additional....

How would the syntax change if I wished to designate a specific Column
two rows below the last data item?

one way:

Cells(Rows.Count, ActiveCell.Column).End(xlUp)(3, 1).FormulaR1C1 = _
"=SUM(R2C:R[-2]C)"

Using 2003 & 97

Need VBA syntax to SUM() at two cells below last data item.

In the code below, I selected the cell using relative then absolute
referencing

ActiveCell.Offset(-3, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-1]C)"

Seeing the above, I am not sure of the syntax for VBA to find the cell two
rows
below the last data item; summing from Row 2 to the last data item and place
an
Excel formula in that cell.

Thoughts?

TIA

Dennis

 

[Dave Peterson]

dim myCol as long
myCol = 5 'column E
Cells(Rows.Count, myCol).End(xlUp)(3, 1).FormulaR1C1 = _
"=SUM(R2C:R[-2]C)"

If I understood correctly.

Thanks! ..... one additional....

How would the syntax change if I wished to designate a specific Column
two rows below the last data item?

one way:

Cells(Rows.Count, ActiveCell.Column).End(xlUp)(3, 1).FormulaR1C1 = _
"=SUM(R2C:R[-2]C)"

Using 2003 & 97

Need VBA syntax to SUM() at two cells below last data item.

In the code below, I selected the cell using relative then absolute
referencing

ActiveCell.Offset(-3, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-1]C)"

Seeing the above, I am not sure of the syntax for VBA to find the cell two
rows
below the last data item; summing from Row 2 to the last data item and place
an
Excel formula in that cell.

Thoughts?

TIA

Dennis

 

[Guest]

Thanks JE & Dave

dim myCol as long
myCol = 5 'column E
Cells(Rows.Count, myCol).End(xlUp)(3, 1).FormulaR1C1 = _
"=SUM(R2C:R[-2]C)"

If I understood correctly.

Thanks! ..... one additional....

How would the syntax change if I wished to designate a specific Column
two rows below the last data item?

one way:

Cells(Rows.Count, ActiveCell.Column).End(xlUp)(3, 1).FormulaR1C1 = _
"=SUM(R2C:R[-2]C)"




Using 2003 & 97

Need VBA syntax to SUM() at two cells below last data item.

In the code below, I selected the cell using relative then absolute
referencing

ActiveCell.Offset(-3, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-1]C)"

Seeing the above, I am not sure of the syntax for VBA to find the cell two
rows
below the last data item; summing from Row 2 to the last data item and place
an
Excel formula in that cell.

Thoughts?

TIA

Dennis

 

[Myrna Larson]

To put SUM formulas in columns A:N

For C = 1 To 14
Cells(65536,C).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R2C:R[-2]C)"
Next C

Clarification:

There are ten different worksheets that will have varying amounts of data
every month.

Therefore, the VBA code needs to place the SUM() at different spots.


***************************

Using 2003 & 97

Need VBA syntax to SUM() at two cells below last data item.

In the code below, I selected the cell using relative then absolute
referencing

ActiveCell.Offset(-3, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-1]C)"

Seeing the above, I am not sure of the syntax for VBA to find the cell two
rows
below the last data item; summing from Row 2 to the last data item and place
an
Excel formula in that cell.

Thoughts?

TIA

Dennis