PDA

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.

jtl
02-23-2010, 01:33 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


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.

jtl
02-23-2010, 02:32 PM
-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"

jtl
02-23-2010, 02:33 PM
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.