Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avg Calculation
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avg Calculation
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avg Calculation
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi threaded calculation (multi CPU) - impact on calculation spe | Excel Discussion (Misc queries) | |||
% calculation | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
range.calculation with UDF not working when calculation is set to automatic | Excel Programming |