Okay, I've generalized my code to make it easier for you to change in the
future if necessary. The controlling parameters (product name and output
column) are now specified in two Const (the
VB keyword for "constant")
statements... the names should tell you what to assign to each. I also
change the data type for the Total (and, now, GrandTotal also) to Double
from the Long I had originally declared them as. This will not affect the
output for the example data you posted, but will allow these two totals to
track pennies as well as dollars if necessary. I also corrected a minor flaw
in how I checked the product names so that now the product names do not have
to all be listed in the same letter casing. The grand totals for the product
being added up in each section is shown 2 rows below the last total in the
specified column (I thought the blank row made the display less confusing).
Sub SumProductbySectionsWithGrandTotal()
Dim Total As Double, GrandTotal As Double
Dim X As Long, FirstRow As Long, LastRow As Long
'
Const OutputColumn As String = "A"
Const ProductName As String = "PRODUCT A"
'
With Worksheets("Sheet1")
FirstRow = .Columns(OutputColumn).Find("*", After:=Cells( _
.Rows.Count, OutputColumn)).Row
LastRow = .Cells(.Rows.Count, OutputColumn).End(xlUp).Row + 1
For X = FirstRow To LastRow
If Len(.Cells(X, "B").Value) = 0 Then
.Cells(X, OutputColumn).Value = Total
GrandTotal = GrandTotal + Total
Total = 0
ElseIf UCase(.Cells(X, "A")) = UCase(ProductName) Then
Total = Total + .Cells(X, "B").Value
End If
Next
.Cells(LastRow + 2, OutputColumn).Value = GrandTotal
End With
End Sub
--
Rick (MVP - Excel)
"Brian" wrote in message
...
One last thing and that should be it :)
How do I sum the total $$ regardless of Product? I would have the sum
entered below in the blank row. Then move down (or up) the sheet and add
the
next group of data. Each group of data is separated by a blank line.
"Rick Rothstein" wrote:
In my code below, the marked line controls where the output goes...
Sub SumProductAbySections()
Dim X As Long, Total As Long, FirstRow As Long, LastRow As Long
With Worksheets("Sheet1")
FirstRow = .Columns("A").Find("*", After:=Cells(.Rows.Count,
"A")).Row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
For X = FirstRow To LastRow
If Len(.Cells(X, "B").Value) = 0 Then
== .Cells(X, "A").Value = Total
Total = 0
ElseIf UCase(.Cells(X, "A")) = "PRODUCT A" Then
Total = Total + .Cells(X, "B").Value
End If
Next
End With
End Sub
Just change the "A" to whichever column you want to the Total to go to.
--
Rick (MVP - Excel)
"Brian" wrote in message
...
This is perfect! What about this modification...
I see the sums of Product A in the cell that I want, how would I put
the
value of Product B next to it? I already copied the VB Module and
entered
"Product B" and that worked. So now I have 2 modules where we can
choose
what
we want to sum (either Product A or B) but I want to plan when they
want
to
do both and have both sums side by side (or just in different cells).
Thanks for your help!
"Rick Rothstein" wrote:
This macro should do what you want (just change the "Sheet1" reference
to
the worksheet name where you want to apply the macro to)...
Sub SumProductAbySections()
Dim X As Long, Total As Long, FirstRow As Long, LastRow As Long
With Worksheets("Sheet1")
FirstRow = .Columns("A").Find("*", After:=Cells(.Rows.Count,
"A")).Row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
For X = FirstRow To LastRow
If Len(.Cells(X, "B").Value) = 0 Then
.Cells(X, "A").Value = Total
Total = 0
ElseIf UCase(.Cells(X, "A")) = "PRODUCT A" Then
Total = Total + .Cells(X, "B").Value
End If
Next
End With
End Sub
--
Rick (MVP - Excel)
"Brian" wrote in message
...
Data starts in A1, dollar amount in Column B.
would like to have the sum in the cell under the product column. I
only
need
the sum for Product A.
"Rick Rothstein" wrote:
In order to avoid your having to come back and ask "How do I modify
your
code for this...?", tell us what columns (letters) your two columns
are,
what row number is your first piece of data on, and tell us where
you
want
the sum to go (under the amounts, in a column next to it, on some
other
worksheet in some other column). Also, did you want the total for
all
products or for each individual product (within each grouping, of
course)?
--
Rick (MVP - Excel)
"Brian" wrote in message
...
I have 2 columns of data and each section is separated by a blank
line.
Each
section doesn't have the same amount of rows. Is there some way
for
a
user
to
hit a button (macro?) that will sum each section?
PRODUCT A $1
PRODUCT A $100
PRODUCT B $50
PRODUCT B $20
PRODUCT A $5000
PRODUCT A $6000
PRODUCT B $50
PRODUCT B $20
PRODUCT B $10
PRODUCT B $2