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!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,164
Messages
5,835,753
Members
430,384
Latest member
kiuwai

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