Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUM function doesn't calculate with blank cells | Excel Discussion (Misc queries) | |||
how to calculate the number of non blank cells from any column? | Excel Worksheet Functions | |||
Formula to calculate number of days & ignore blank cells | Excel Discussion (Misc queries) | |||
excel does not calculate sums correctly | Excel Discussion (Misc queries) | |||
How do I find cells that being with a blank? | Excel Discussion (Misc queries) |