Checkbox linked to calcs
 

Hi, I'm trying to use a checkbox to either include a certain data point or
exclude it. If checkbox is checked, n=n-a, if checkbox is unchecked, n.

I tried running the following code, but it doesn't work. Can someone please
help?

Dim withMobile As CheckBox

If withMobile.Value = True Then

Range("U66").Select
ActiveCell.FormulaR1C1 = "=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]-RC[108]"
Selection.AutoFill Destination:=Range("U66:U99"), Type:=xlFillDefault

Range("V66").Select
ActiveCell.FormulaR1C1 = "=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]-RC[108]"
Selection.AutoFill Destination:=Range("V66:V99"), Type:=xlFillDefault

Range("W66").Select
ActiveCell.FormulaR1C1 = _

"=IF(FISCAL_YTD=""YES"",RC[-2]-RC[-1],IFERROR(RC[-2]-RC[-1],0))-RC[108]"
Selection.AutoFill Destination:=Range("W66:W99"), Type:=xlFillDefault

Range("L66").Select
ActiveCell.FormulaR1C1 = "=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]-RC[108]"
Selection.AutoFill Destination:=Range("L66:L99"), Type:=xlFillDefault

ElseIf withMobile.Value = False Then

Range("U66").Select
ActiveCell.FormulaR1C1 = "=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]"
Selection.AutoFill Destination:=Range("U66:U99"), Type:=xlFillDefault

Range("V66").Select
ActiveCell.FormulaR1C1 = "=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]"
Selection.AutoFill Destination:=Range("V66:V99"), Type:=xlFillDefault

Range("W66").Select
ActiveCell.FormulaR1C1 = _
"=IF(FISCAL_YTD=""YES"",RC[-2]-RC[-1],IFERROR(RC[-2]-RC[-1],0))"
Selection.AutoFill Destination:=Range("W66:W99"), Type:=xlFillDefault

Range("L66").Select
ActiveCell.FormulaR1C1 = "=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]"
Selection.AutoFill Destination:=Range("L66:L99"), Type:=xlFillDefault

End If


End Sub

Thank you,

MK

Checkbox linked to calcs
 

Check boxes sit on top of the worksheet an cannot directly effect
functions on the worksheet. You need to set the Linked Cell property of
the check box to add either True or False to a new column in the
worksheet. So if you put a true or false in colmn b then modify you
formula as follows

from
ActiveCell.FormulaR1C1 =
"=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]-RC[108]"

to
ActiveCell.FormulaR1C1 =
"=if(RC[2]=True,RC[60]+RC[90]+RC[102]+RC[114]+RC[126]-RC[108],0)"


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183466

Microsoft Office Help