From: Buddy on 19 Feb 2010 13:56 Please ignore this post. I mistakenly posted in the wrong forum. I will repost in correct forum. Please ignore this post. Thank you. "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?  |