Prev: Timesheet Calculation
Next: Reference text in a cell inside a formula to specify a sheet name
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?
 | 
Pages: 1
Prev: Timesheet Calculation
Next: Reference text in a cell inside a formula to specify a sheet name