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!