Stdev of Subtotaled numbers VB Macro

[mikero]

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!

 

[Andrew Poulsom]

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]

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!