combinations of checkboxes and executing commands

**dreamz** · 10-27-2005, 04:55 PM

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.

edit: nevermind, i got it. i defined variables so that it is a number if the checkbox is checked, and 0 otherwise. then i defined sum and count as the sum of those variables.

**dreamz** · 10-27-2005, 06:29 PM

nevermind, i have a question.

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

Alok · 10-28-2005, 10:05 AM

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

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
>
>

**dreamz** · 10-28-2005, 10:34 AM

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

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 If

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 the formula because i'm still testing it).

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

Alok · 10-27-2005, 07:05 PM

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
>
>