VBA Inserting formula in cell: sum unknown number of cells

[jcb]

Hi, I am trying to get a macro to insert a formula in a cell that sum the all the cells in the column above the active cell. The problem is that the number of rows could change, and I don't know how to fix the r1c1 for the first cell...
This is what I've got so far:
ActiveCell.FormulaR1C1 = "=sum(R[????]:R[-2]C[0])"

[JBeaucaire]

ACTIVECELL usually points to a bad method, VBA has no need to activate a cell to place values into it. So your snippet makes me want to see the whole macro.

Meanwhile, probably this:

Please Login or Register  to view this content.

[jcb]

ACTIVECELL usually points to a bad method, VBA has no need to activate a cell to place values into it. So your snippet makes me want to see the whole macro.

Meanwhile, probably this:

Please Login or Register  to view this content.

Hi there, it probably is a bad method as I am still learning, so your advice is much appreciated. This is what the Excel sheet looks like:

A B C
1
2
3 70 1 70
4 70 2 140
5 70 3 210
6 70 4 280
7 70 5 350
8 70 6 420
9 70 7 490
10 70 8 560
11 70 9 630
12 70 10 700
13 70 11 770
14 70 12 840
15 70 13 910
16
17 0
18 0
19 0

It is the formula that needs to go in c17 that I am struggling with. The number of rows might alter so the formula needs to be adaptable.This is my whole method:

Sub Macro1()
'
' Macro1 Macro
'

'
Range("c3").Select
Do Until ActiveCell.Value = ""
ActiveCell.FormulaR1C1 = "=R[0]C[-2]*R[0]C[-1]"
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell.Offset(1, 0).Select

ActiveCell.FormulaR1C1 = "=sum(?????:R[-2]C[0])"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=sum(R[-1]C[0]*0.2)"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=sum(R[-2]C[0]+R[-1]C[0])"
ActiveCell.Offset(1, 0).Select

End Sub

Problem bit highlighted.
If I use you suggestion it seems to go to the next row down...

Thanks for your time on this!!!

[JBeaucaire]

There's no need for a macro for this, is there? You can place a formula in the first cell and double-click the lower-right corner to instantly put the formula in the whole column. In C3:

=SUM($A$2:$A3)

[jcb]

Hi,

I can't I'm afraid. This is a small part of a bigger macro that copies a worksheet to a blank sheet, and then automatically inserts formulas in the required cells. As mentioned before my problem is that the number of rows might change so the "sum" formula needs to be inserted in the correct cell.

With the Activecell.Offset and Formula R1C1 method I can specify where the range ends for the "sum" function, but it is the beginning of the range that I am struggling with.
Normally if it is a known number of rows you can insert"=sum(R[-200]C[0]:R[-1]C[0])",
but I don't know if it is 200 lines long...

Thanks for your attempt though!!

[JBeaucaire]

Where is the first row each time? Row1, row2?

[jcb]

The first row is allways Row 3.

[jcb]

Not to worry, I have managed to piece this together from various other posts, and it seems to work!!!

ActiveCell.FormulaR1C1 = "=sum(R[-" & Cells(Rows.Count, "B").End(xlUp).Row & "]c[0]:R[-2]C[0])"

Of course if anyone can think of an easier way I'd be very interested!

Thanks for all your help - much appreciated.

[JBeaucaire]

If the first row is always row 3 and the end is unknown, then this (based on my original suggestion) works:

Please Login or Register  to view this content.