Hi, how can I write code for counting numbers which are sometimes in
three, for or five?

Example:
1 2 3 4
A 12
B 14
C 25
D sum
E 35
F 64
G sum
H 87
I 12
J 46
K 77
L sum

I recorded this macro for one sum(f.e. in D1) and the code always
records the exact figure in three, so this code is not applicable to
other sums (G1 or L1)

This is the wrong macro:
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"



Any idea?

Thanx Marian

Marian,

If the Activecell is where you want to put the formula, and there is a
list of at least two numbers above the activecell, then

Sub CreateSumFormula2()
With ActiveCell
..Formula = "=Sum(" & Range(.Offset(-1, 0), _
.Offset(-1, 0).End(xlUp)).Address(False, False) & ")"
End With
End Sub

HTH,
Bernie
MS Excel MVP

"Marian" wrote in message
...
Hi, how can I write code for counting numbers which are sometimes in
three, for or five?

Example:
1 2 3 4
A 12
B 14
C 25
D sum
E 35
F 64
G sum
H 87
I 12
J 46
K 77
L sum

I recorded this macro for one sum(f.e. in D1) and the code always
records the exact figure in three, so this code is not applicable to
other sums (G1 or L1)

This is the wrong macro:
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"



Any idea?

Thanx Marian