Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - VBA sub routine to get AVG
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - VBA sub routine to get AVG
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - VBA sub routine to get AVG
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
interpolation routine for Excel | Excel Worksheet Functions | |||
Converting a Access routine into an Excel macro? | Excel Discussion (Misc queries) | |||
how can I use excel to automatically generate routine report? | Excel Worksheet Functions | |||
Help: a routine for launching an allarm in excel | Excel Programming | |||
Need VBA Routine | Excel Programming |