Menu

Using VBA, subtotal a column only if there is more than one qualifier

Search this Thread

#1

Report Post

Posted to microsoft.public.excel.programming

Phillip R

Phillip R is offline

external usenet poster

Posts: 2

Default

Using VBA, subtotal a column only if there is more than one qualifier

(Joanne) wrote in message . com...
I need to do a subtotal for a very large spreadsheet, but I don't want
to subtotal the numbers when there is only one qualifier since it is a
little redundant. Essentially a summarized version of my spreadsheet
is:
A B
101 25
101 27
101 89
201 45
301 96
301 37

What I want the macro to do is; subtotal Column B for every change in
column A except when Column A only has one qualifier, therefore the
final spreadsheet should look something like this:
A B
101 25
101 27
101 89
Sum 101 141
201 45
301 96
301 37
Sum 301 133
Any help would be very appreciated and Thank-you in advace.
Joanne

Assuming that there is a continuous column of numbers starting with A1
and a list of numbers in column B, then this code worked for me

Sub DoSubTotal()
Dim rng As Range
Dim k As Integer
Dim kntdups As Integer

Set rng = Range("A:A")
k = 1
kntdups = 0
Do While rng.Cells(k).Value < ""
Do
If rng.Cells(k) = rng.Cells(k + 1) Then
kntdups = kntdups + 1
k = k + 1
Else
If kntdups = 1 Then
With rng
.Cells(k + 1).EntireRow.Insert
.Cells(k + 1) = "Subtotal " & .Cells(k)
..Cells(k + 1).Offset(0, 1).FormulaR1C1 = "=SUM(R[-" & kntdups + 1 & "]C:R[-1]C)"
End With
kntdups = 0
k = k + 2
Else
kntdups = 0
k = k + 1
End If
End If
Loop Until kntdups = 0
Loop
End Sub

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
need qualifier statement	Abbey Somebody	Excel Worksheet Functions	5	May 17th 09 03:11 PM
countif formula: 2nd column qualifier	AJB	Excel Discussion (Misc queries)	6	December 27th 07 05:21 PM
Add qualifier to andif statement	litngldy	New Users to Excel	4	November 8th 06 10:20 PM
setting text qualifier in Excel	Lyle	Excel Discussion (Misc queries)	1	June 8th 05 06:28 PM
IF function using a dates as the qualifier..	Qaspec	Excel Worksheet Functions	2	January 19th 05 09:27 PM

All times are GMT +1. The time now is 08:14 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement