Irregular areas of numbers

[Marian]

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

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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(xlCellTypeBlanks)
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

 

[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(xlCellTypeBlanks)
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





post -

THANX Bernie