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

Marian,

With the activecell in column B, the macro below will do what you
want.

HTH,
Bernie
MS Excel MVP

Sub CreateSumFormulaInEachBlankCellOfCurrentColumn()
Dim myForm As String
Dim i As Integer

With ActiveCell.EntireColumn.SpecialCells(xlCellTypeBla nks)
For i = .Areas.Count To 1 Step -1
With .Areas(i).Cells(1)
If .Row < 1 Then
myForm = "=Sum(" & Range(.Offset(-1, 0), _
.Offset(-1, 0).End(xlUp)).Address(False, False) & ")"
.Formula = myForm
End If
End With
Next i
End With

End Sub



Hi Bernie,

I tried it and it didn't work. I attached the Excel file to this
post -
in yellow cells there I need sums. Could you please see at it?

THANX

Marian

Bernie Deitrick napsal(a):
Marian,

With the activecell in column B, the macro below will do what you
want.

HTH,
Bernie
MS Excel MVP

Sub CreateSumFormulaInEachBlankCellOfCurrentColumn()
Dim myForm As String
Dim i As Integer

With ActiveCell.EntireColumn.SpecialCells(xlCellTypeBla nks)
For i = .Areas.Count To 1 Step -1
With .Areas(i).Cells(1)
If .Row < 1 Then
myForm = "=Sum(" & Range(.Offset(-1, 0), _
.Offset(-1, 0).End(xlUp)).Address(False, False) & ")"
.Formula = myForm
End If
End With
Next i
End With

End Sub




Hi Bernie,

I tried it and it didn't work. I attached the Excel file to this

post -

in yellow cells there I need sums. Could you please see at it?

THANX

Marian




THANX Bernie :-)