View Full Version : Tough one
mzsuga
02-23-2010, 12:51 PM
I have a subtotal but subtotals can only show 1 groupby. I want a column to be average instead. Is there any way to do that? I was thinking maybe finding the word total in column A since subtotal has total in it then inserting a formula in column F to find the average above. This would work for the first subtotal since it can to average to the first row, but how about the other subtotals? Is there a way to average a subtotal until the word subtotal appears?
So if row 500 has the word subtotal in it which totals up row 300-499, then can I average row 300-499 based on the existence of the word subtotal in row 299?
p45cal
02-23-2010, 12:55 PM
Consider using a Pivot Table.
Try this out. Assuming that the word "Subtotal" is located in column 1:
Sub sample()
Dim subtotalCol As Integer, averagecol As Integer
Dim firstRowtoAverage As Long, subtotalRow As Long
Dim x As Long, findtotal As Integer
subtotalCol = 2
averagecol = 3
findtotal = WorksheetFunction.CountIf(Columns(1), "Subtotal")
If findtotal <> 0 Then
firstRowtoAverage = 2
For x = 1 To findtotal
subtotalRow = Columns(1).Find(What:="Subtotal", _
After:=Cells(firstRowtoAverage, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
Cells(subtotalRow, averagecol).FormulaR1C1 = "=average(R" & firstRowtoAverage & "C" & subtotalCol & ":R" & subtotalRow - 1 & "C" & subtotalCol & ")"
firstRowtoAverage = subtotalRow + 1
Next x
Else
MsgBox ("Subtotal does not exist")
End If
End Sub
Edit Lucas: VBA tags added to code.
mzsuga
02-23-2010, 01:52 PM
Try this out. Assuming that the word "Subtotal" is located in column 1:
Sub sample()
Dim subtotalCol As Integer, averagecol As Integer
Dim firstRowtoAverage As Long, subtotalRow As Long
Dim x As Long, findtotal As Integer
subtotalCol = 2
averagecol = 3
findtotal = WorksheetFunction.CountIf(Columns(1), "Subtotal")
If findtotal <> 0 Then
firstRowtoAverage = 2
For x = 1 To findtotal
subtotalRow = Columns(1).Find(What:="Subtotal", After:=Cells(firstRowtoAverage, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
Cells(subtotalRow, averagecol).FormulaR1C1 = "=average(R" & firstRowtoAverage & "C" & subtotalCol & ":R" & subtotalRow - 1 & "C" & subtotalCol & ")"
firstRowtoAverage = subtotalRow + 1
Next x
Else
MsgBox ("Subtotal does not exist")
End If
End Sub
subtotalCol = 2
averagecol = 3
For that part, the averagecol should equal to 5 since it's in column F, is that right?
Also do you know how to divide by the last cell in column B? =h7/last cell in column B?
Edit: VBA tags added to code.
lucas
02-23-2010, 02:15 PM
Both of you seem fairly new to the forum.
If you select your code when posting and hit the green vba button, your code will be formatted for the forum.
-That's correct.
-You can use Find to locate the last non-blank row in your sheet and store the row number into a variable. Example:
lastRow = cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Cells(x, y).FormulaR1C1 = "=H7/R" & lastRow & "C2"
yes, pretty new to this site. Thanks for the info lucas!
lucas
02-23-2010, 02:36 PM
no problem jtl, enjoy your time here.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.