Find blank cells and calculate sums
 

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

Find blank cells and calculate sums
 

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
.

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
.

Find blank cells and calculate sums
 

Are all the cells with numbers just values or are do they include formulas?

If they're all plain old numbers, then this might work:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myArea As Range

With ActiveSheet
Set myRng = .Range("H1", _
.Cells(.Rows.Count, "H").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeConstants)

For Each myArea In myRng.Areas
With myArea
With .Cells(1, 1).Offset(.Rows.Count)
'.Value = Application.Sum(myArea)
.Formula = "=sum(" & myArea.Address(0, 0) & ")"
.Font.Bold = True
End With
End With
Next myArea
End With
End Sub

There's a line that's commented out. '.Value =...

I wasn't sure if you wanted to plop in the values or plop in a formula. If you
just want the value, then delete the .formula line. If you want the formula
(that will change if you change the values, then delete the .value line.



AlisonB wrote:

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

--

Dave Peterson

Find blank cells and calculate sums
 

I get an error message -

Compile Error:

Syntax error

and the code

lastcell.Offset(1, coloffset).Value = _
Application.WorksheetFunction.Sum(Range(firstcell,
lastcell))

is in red.

Does this help spot what is wrong?

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
.

Find blank cells and calculate sums
 

Make sure the entire line from "Application" to "lastcell))" is on
one line in the VBE.

In article ,
(AlisonB) wrote:

I get an error message -

Compile Error:

Syntax error

and the code

lastcell.Offset(1, coloffset).Value = _
Application.WorksheetFunction.Sum(Range(firstcell,
lastcell))

is in red.

Does this help spot what is wrong?