Find blank cells and calculate sums
Hi Patrick,
I don't know if I am doing something wrong but it isn't working
properly. To explain, below shows the start of the columns after I
have run the macro -
Col H Col I
100
75
75
75 150.0
50
100
75
75
75
75 150.0
75
100
100
75
75
75 150.0
75
50 150.0
75
75 125.0
25
50
75
100
100
75
175.0
Any ideas?
Kind regards,
Alison
"Patrick Molloy" wrote in message ...
Option Explicit
Sub addSubTotals()
Dim firstcell As Range
Dim lastcell As Range
Dim coloffset As Long
coloffset = 1
Set lastcell = Range("H65000").End(xlUp)
Do
Set firstcell = lastcell.End(xlUp)
lastcell.Offset(1, coloffset).Value = _
Application.WorksheetFunction.Sum(Range(firstcell,
lastcell))
lastcell.Offset(1, coloffset).Font.Bold = True
Set lastcell = firstcell.End(xlUp)
Loop While lastcell.Row 1
End Sub
Copy the code to a standard module...like the default
Module1 that th emacro recorder creates.
I offset the result one column to the right. if you want
the result in the gaps, change the value in the code for
the coloffset variable from 1 to 0 (zero)
Patrick Molloy
Microsft Excel MVP
-----Original Message-----
Hi,
I am a newbie to macros, etc. using the macro recorder
where possible.
I have a column of data (Col H) and where a cell is
blank, I want to
calculate the sum of the cells since the previous
blank. For example,
100
75
100
Blank(Answer here should be 275)
25
0
100
Blank(Answer here should be 125)
100
Blank(Answer here should be 100)
75
50
Blank(Answer here should be 125)
I don't need any grand total.
I am using Excel 2000 with Windows 2000.
Can anyone help?
Kind regards,
Alison
.
|