combinations of checkboxes and executing commands
 

let's say i have 4 checkboxes, a, b, c, and d. i want to do some
calculation (e.g. the average) based on what is selected.

in other words, if a and b are chosen, it will be the average of a and
b. if a, b, and c are chosen, then it will be the average of a, b, and
c.

now, of course, if i hard code this as a bunch of if-then statements,
it will be cumbersome, as i'll have to code every single possible
combination (a; b; c; d; a and b; a and c; etc.).

is there a more efficient way to do this?

thanks.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=479943

combinations of checkboxes and executing commands
 

nevermind, i have a question.

how do i make it so that the formula is pasted into a cell rather than
a value?


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=479943

combinations of checkboxes and executing commands
 

if chkA.value then
wcount=wcount+1
dValue = dValue + A
endif
if chkB.value then
wcount=wcount+1
dValue = dValue + B
endif
if chkC.value then
wcount=wcount+1
dValue = dValue + C
endif

and so on.
then average is dvalue/wcount.
Hope this helps.

Alok

"dreamz" wrote:


let's say i have 4 checkboxes, a, b, c, and d. i want to do some
calculation (e.g. the average) based on what is selected.

in other words, if a and b are chosen, it will be the average of a and
b. if a, b, and c are chosen, then it will be the average of a, b, and
c.

now, of course, if i hard code this as a bunch of if-then statements,
it will be cumbersome, as i'll have to code every single possible
combination (a; b; c; d; a and b; a and c; etc.).

is there a more efficient way to do this?

thanks.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=479943

combinations of checkboxes and executing commands
 

Hi,
The way to do it is
Worksheets("abc").Cells(1,2).FormulaR1C1="=Sum(myr ange)"

By the way when you are asking a new question it is advisable to start a new
thread. Otherwise people may not pay attention to your new query. :-)

Alok


"dreamz" wrote:


nevermind, i have a question.

how do i make it so that the formula is pasted into a cell rather than
a value?


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=479943

combinations of checkboxes and executing commands
 

thanks, alok. i wrote something like this. to be clear, i declare
tricor and oos as ranges.


Code
-------------------
If radOverall Then

If chTricor Then
Set Tricor = Range("RankTricorAll")
TricorCount = 1
Else
Set Tricor = shRankings.Range("B3")
TricorCount = 0
End If

If chOOS Then
Set OOS = Range("RankOOSAll")
OOSCount = 1
Else
Set OOS = shRankings.Range("B3")
OOSCount = 0
End If

shRankings.Range("AverageAll").Formula = Tricor + OOS

End I
-------------------


and that didn't work. it said type mismatch error. what did i do wrong
(don't worry about the count stuff for now. i didn't include it in th
formula because i'm still testing it).


and it's not an entirely new question since it's just a problem
encountered when trying to implement the solution to this one. i don'
like creating multiple threads, as it seems like spam

--
dream
-----------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...fo&userid=2646
View this thread: http://www.excelforum.com/showthread.php?threadid=47994