Sub Inspectthis()

Dim F As String
Dim I As Integer
Dim PrevRow As Long
Dim R As Long
Dim Rng As Range
Dim RngEnd As Range
Dim SumArray As Variant
Dim Wks As Worksheet

Set Wks = Worksheets("Sheet1")

SumArray = Array("O", "R", "U", "X", "AA", "AD", "AG")

Set Rng = Wks.Range("A2")
Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))

PrevRow = 2

For R = 2 To Rng.Rows.Count
If Rng.Item(R) = "Renovation" Then
For I = 0 To UBound(SumArray)
F = "=SUM(" & SumArray(I) & PrevRow & ":" & SumArray(I) & R & ")"
Wks.Cells(R + 1, SumArray(I)).Formula = F
Next I
PrevRow = R
End If
Next R

End Sub

The macro above will inspect every row in Column A for the text Renovation.
When the text Renovation is found the average formula, =Average(Range:Range)
will be inserted in the same row in Columns O, R, U, X, AA, AD, AG so that
all the rows above the formula with numbers will be included in the
calculation just as if I clicked the AutoSum icon and set it to average. The
problem I am having with macro above is that the formula range seems to be
grabbing into the calculation 1 extra row above what it should be including
in the formula which is messing up the computation. Can you help me fix this
macro so that it stops grabbing the 1 extra row above so the calculation is
correct?

Try out the below macro

Sub MyMacro()
Dim ws As Worksheet, lngRow As Long, lngStartRow As Long

lngStartRow = 2
Set ws = Worksheets("Sheet1")

For lngRow = lngStartRow To ws.Cells(Rows.Count, "A").End(xlUp).Row
If UCase(ws.Range("A" & lngRow)) = UCase("Renovation") Then
For lngCol = 15 To 33 Step 3
ws.Cells(lngRow, lngCol).FormulaR1C1 = _
"=SUM(R[-" & lngRow - lngStartRow & "]C:R[-1]C)"
Next
lngStartRow = lngRow + 1 'mark this line if you need cummulative totals
End If
Next
End Sub



--
Jacob


"Buddy" wrote:

> Sub Inspectthis()
>
> Dim F As String
> Dim I As Integer
> Dim PrevRow As Long
> Dim R As Long
> Dim Rng As Range
> Dim RngEnd As Range
> Dim SumArray As Variant
> Dim Wks As Worksheet
>
> Set Wks = Worksheets("Sheet1")
>
> SumArray = Array("O", "R", "U", "X", "AA", "AD", "AG")
>
> Set Rng = Wks.Range("A2")
> Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
> Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))
>
> PrevRow = 2
>
> For R = 2 To Rng.Rows.Count
> If Rng.Item(R) = "Renovation" Then
> For I = 0 To UBound(SumArray)
> F = "=SUM(" & SumArray(I) & PrevRow & ":" & SumArray(I) & R & ")"
> Wks.Cells(R + 1, SumArray(I)).Formula = F
> Next I
> PrevRow = R
> End If
> Next R
>
> End Sub
>
> The macro above will inspect every row in Column A for the text Renovation.
> When the text Renovation is found the average formula, =Average(Range:Range)
> will be inserted in the same row in Columns O, R, U, X, AA, AD, AG so that
> all the rows above the formula with numbers will be included in the
> calculation just as if I clicked the AutoSum icon and set it to average. The
> problem I am having with macro above is that the formula range seems to be
> grabbing into the calculation 1 extra row above what it should be including
> in the formula which is messing up the computation. Can you help me fix this
> macro so that it stops grabbing the 1 extra row above so the calculation is
> correct?
>

Jacob, I think you should change your name to Mr. Wizard because you are the
man! This operates beautifully. Thank you!

"Jacob Skaria" wrote:

> Try out the below macro
>
> Sub MyMacro()
> Dim ws As Worksheet, lngRow As Long, lngStartRow As Long
>
> lngStartRow = 2
> Set ws = Worksheets("Sheet1")
>
> For lngRow = lngStartRow To ws.Cells(Rows.Count, "A").End(xlUp).Row
> If UCase(ws.Range("A" & lngRow)) = UCase("Renovation") Then
> For lngCol = 15 To 33 Step 3
> ws.Cells(lngRow, lngCol).FormulaR1C1 = _
> "=SUM(R[-" & lngRow - lngStartRow & "]C:R[-1]C)"
> Next
> lngStartRow = lngRow + 1 'mark this line if you need cummulative totals
> End If
> Next
> End Sub
>
>
>
> --
> Jacob
>
>
> "Buddy" wrote:
>
> > Sub Inspectthis()
> >
> > Dim F As String
> > Dim I As Integer
> > Dim PrevRow As Long
> > Dim R As Long
> > Dim Rng As Range
> > Dim RngEnd As Range
> > Dim SumArray As Variant
> > Dim Wks As Worksheet
> >
> > Set Wks = Worksheets("Sheet1")
> >
> > SumArray = Array("O", "R", "U", "X", "AA", "AD", "AG")
> >
> > Set Rng = Wks.Range("A2")
> > Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
> > Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))
> >
> > PrevRow = 2
> >
> > For R = 2 To Rng.Rows.Count
> > If Rng.Item(R) = "Renovation" Then
> > For I = 0 To UBound(SumArray)
> > F = "=SUM(" & SumArray(I) & PrevRow & ":" & SumArray(I) & R & ")"
> > Wks.Cells(R + 1, SumArray(I)).Formula = F
> > Next I
> > PrevRow = R
> > End If
> > Next R
> >
> > End Sub
> >
> > The macro above will inspect every row in Column A for the text Renovation.
> > When the text Renovation is found the average formula, =Average(Range:Range)
> > will be inserted in the same row in Columns O, R, U, X, AA, AD, AG so that
> > all the rows above the formula with numbers will be included in the
> > calculation just as if I clicked the AutoSum icon and set it to average. The
> > problem I am having with macro above is that the formula range seems to be
> > grabbing into the calculation 1 extra row above what it should be including
> > in the formula which is messing up the computation. Can you help me fix this
> > macro so that it stops grabbing the 1 extra row above so the calculation is
> > correct?
> >