Sub BuildFormulas()

Dim oldAr As Range, rng As Range, lastRow As Range

Dim icol As Range, rng1 As Range, rng2 As Range

Dim rng3 As Range, rng4 As Range

Dim ar As Range, ar1 As Range

Set rng = Range(Cells(4, "D"), Cells(Rows.Count, "D").End(xlUp))

Set lastRow = rng(rng.Count).Offset(1, 0)

Set rng1 = rng.SpecialCells(xlConstants)

' determine extent of the data

Set rng2 = Range(rng1.Areas(1)(1, 2), rng1.Areas(1)(1, 2).End(xlToRight))

Set icol = Columns(rng2(1).Column).EntireColumn

For Each ar In rng1.Areas

Set ar1 = Intersect(ar.EntireRow, icol)

ar1.Offset(-1, 0).Resize(1, rng2.Columns.Count).Formula = _

"=Subtotal(9," & ar1.Address(1, 0) & ")"

Next

Set rng1 = rng.Offset(0, -2).SpecialCells(xlConstants)

Set rng1 = Union(rng1, Intersect(lastRow.EntireRow, _

rng1.EntireColumn))

For Each ar In rng1.Areas

If ar.Address <> rng1.Areas(1).Address Then

Set rng3 = Range(oldAr(2), ar(1)(0))

Set rng3 = Intersect(rng3.EntireRow, icol)

oldAr.Offset(0, 3).Resize(1, rng2.Columns.Count).Formula = _

"=Subtotal(9," & rng3.Address(1, 0) & ")"

End If

Set oldAr = ar

Next

Set ar = Intersect(lastRow.EntireRow, Columns(1))

Set oldAr = Cells(4, "A")

Set rng3 = Range(oldAr(2), ar(1)(0))

Set rng3 = Intersect(rng3.EntireRow, icol)

oldAr.Offset(0, 4).Resize(1, rng2.Columns.Count).Formula = _

"=Subtotal(9," & rng3.Address(1, 0) & ")"

Set rng4 = Columns(2).Find("Yes")

With lastRow.Offset(1, 1)

.Value = "Q1"

.Font.Bold = True

.Offset(1, 0).Formula = _

Application.Substitute("=Sum(E4:G4)", 4, rng4.Row)

With .Offset(0, 3)

.Value = "Q2"

.Font.Bold = True

.Offset(1, 0).Formula = _

Application.Substitute("=Sum(H4:J4)", 4, rng4.Row)

End With

End With

End Sub

--

Regards,

Tom Ogilvy

"Tom Ogilvy" <(E-Mail Removed)> wrote in message

news:(E-Mail Removed)...

> send me some actual sample data in a workbook and color the cells where
you

> want a subtotal in blue and the cells to subtotal in red.

>

> Indicate in green where you want the summary of the subtotals.

>

> (E-Mail Removed)

>

> --

> Regards,

> Tom Ogilvy

>

>

> "(E-Mail Removed)" wrote:

>

> > Well, I could not get to the answer that I needed using the above

> > advice so I thought I would try a differenct tactic.

> >

> > I need to sum a range of #'s that will vary in size. I would want the

> > macro to find the Yes in Col. B and then, move down to the Col. D

> > (ignoring the subtotals from the Col. C categories) subcategories

> > (contract labor, etc.) and sum up these lines for 12 columns (12

> > months). I would then, need to skip other the second category (Expense

> > Ongoing) and sum up the subcategories (equip. depr, etc.)

> >

> > Any ideas?

> >

> >