Stdev of Subtotaled numbers VB Macro

mikero

New Member
Joined
Aug 28, 2007
Messages
2
Good morning,

I have used the Subtotal function to Sum large amounts of price, cost, and quantity data by product name. Now I would like to add the standard deviation of job costs.

I have tried to create a macro that will detect if a row is a subtotal, by checking if the font is bold. If the cell is not bold, it counts 1 and offsets one row. If the cell is bold, it makes the entirerow bold, then offsets 11 columns to the cell where I want to input the formula.

This is where I have a problem. I want the formula to select the subtotaled costs and find the stdev. Here is what I have so far:

Sub stdevnbold()
'
' stdevnbold Macro
'

'
Dim howmany As Long
howmany = 0
Do Until ActiveCell = ""
If Selection.Font.Bold = False Then
howmany = howmany + 1
ActiveCell.Offset(1, 0).Range("A1").Select
Else
ActiveCell.EntireRow.Font.Bold = True
ActiveCell.Offset(0, 11).Range("a1").Select
ActiveCell.FormulaR1C1 = "=stdev(r[-howmany]c[-1]:r[-1]c[-1])"
ActiveCell.Offset(1, -11).Range("a1").Select
howmany = 0
End If
Loop
End Sub


The R1C1 formula does not work. I believe that the "-howmany" is not being read correctly. At that line, marked in Italics, I get the message "Run-Time Error '1004': Application-Defined or Object-Defined Error".

Does anyone know what I am doing wrong? I would greatly appreciate any help.

Thanks!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Maybe try:

ActiveCell.FormulaR1C1 = "=stdev(r[-" & howmany & "]c[-1]:r[-1]c[-1])"

Note that the SUBTOTAL function contains standard deviation as Function_Num = 7.
 

mikero

New Member
Joined
Aug 28, 2007
Messages
2
Mr. Poulson,

Thank you very much for the help. That worked perfectly. This is not the first time that I have tried, and failed, to use a counting mechanism in a r1c1 formula. Henceforth, I will be able.

The revision to the R1C1 did not solve all problems. I would get a #DIV/0 error if there was only one job being subtotaled, so I added an additional IF. I also adjusted the formula to provide the stdev as a % of average cost, since product costs vary so greatly.

Here is the updated formula which works flawlessly:

Sub stdevnbold()
'
' stdevnbold Macro
'

'
Dim howmany As Long
howmany = 0
Do Until ActiveCell = ""
If Selection.Font.Bold = False Then
howmany = howmany + 1
ActiveCell.Offset(1, 0).Range("A1").Select
Else
ActiveCell.EntireRow.Font.Bold = True
ActiveCell.Offset(0, 11).Range("a1").Select
If howmany > 1 Then
ActiveCell.FormulaR1C1 = "=stdev(r[-" & howmany & "]c[-1]:r[-1]c[-1])/rc[-1]"
Else
ActiveCell.FormulaR1C1 = "0"
End If
ActiveCell.Offset(1, -11).Range("a1").Select
howmany = 0
End If
Loop
End Sub

Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,217
Messages
5,594,887
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top