Excel - VBA sub routine to get AVG

[Excel-erate2004]

Hello,

I had developed a sub routine that sums a range of cells M12:M40 tha
depended on whether or not a checkbox value was true for tha
particular cell.

I.E. There are 29 cells between M12 and M40 and there is a Checkbo
associated with each cell labled CheckBox1 to CheckBox29.

What I would like to be able to do is take that sum and then get a
average based on those checkboxes that are checked.

Here is my code:


Private Sub SumCheckBoxes_Click()

Dim obj As OLEObject
Dim dblValue As Double

For Each obj In ActiveSheet.OLEObjects
If obj.ProgId = "Forms.CheckBox.1" And obj.Object.Value Then
dblValue = dblValue + Range _
("M" & VBA.Replace(obj.Name, "CheckBox", "") + 11).Value
End If
Next obj

'MsgBox dblValue
Sheets("Step 5").Range("M43") = dblValue

End Sub


What I need is some way to count the number of cells that have thei
checkbox value set to true then divide that number by the sum from th
code above.

How could I go about this? Any ideas?

Thanks for any help I can ge

 

[Melanie Breden]

I had developed a sub routine that sums a range of cells M12:M40 that

depended on whether or not a checkbox value was true for that
particular cell.
What I would like to be able to do is take that sum and then get an
average based on those checkboxes that are checked.

here is the extension of my code:

Sub AverageCheckBoxes()
Dim obj As OLEObject
Dim dblValue As Double
Dim intCount As Integer

For Each obj In ActiveSheet.OLEObjects
If obj.progID = "Forms.CheckBox.1" And obj.Object.Value Then
dblValue = dblValue + Range _
("M" & VBA.Replace(obj.Name, "CheckBox", "") + 11).Value
intCount = intCount + 1
End If
Next obj

MsgBox dblValue / intCount
End Sub

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Excel-erate2004]

Thanks Melanie!

My problem was in the placing of this line:

intCount = intCount + 1.

But now it works

Thanks for your time and suggestion, it works perfectl