Solved: IF Statement Limit Reached

[gimli]

Hi all,

I have a formula that may have reached the limit for IF statements. Does VBA allow more IF statements per cell?

The first IF is getting rid of a Value Error

If so...can someone suggest code for:

This formula is in CELL E13

=IF(F13=" "," ",IF(OR(BY15="1",BY15="2"),EA14,IF(BY15="3",CG16,IF(BY15="4",CG17,IF(BY15=" 5",CQ16,IF(BY15="6",CQ17,IF(BY15="7",CQ18,IF(BY15="8",DC14,IF(BY15="9",DO14 ,))))))))

Thanks much

[austenr]

Yep your over the limit. 7 is the limit. I counted 9. As far as correcting the formula, XLD is the man. Hope he comes by to assist (no disrrespect to any of my other fellow VBA pals).

[austenr]

You might want to consider Select Case as in this example:

[vba]
Select Case < Expression to test> Case Do something
Case Else Do something else
End Select[/vba]

Also give this link a look

http://www.ozgrid.com/VBA/select-case.htm

[mdmackillop]

Have a look here

[austenr]

Forgot about ole Chip Malcomb.

[gimli]

Ok..im learning..great ideas..one more thing....how do I assign this case statement to a cell like a formula? The formula im replacing is in E13. This will work only when I run the SUB.

[VBA]
Sub Candyman1()
Select Case Range("BY15").Value
Case "A"
Range("E14").Value = Range("CG16").Value
Case "B"
Range("E14").Value = Range("CG17").Value
Case "C"
Range("E14").Value = Range("CR16").Value
Case "D"
Range("E14").Value = Range("CR17").Value
Case "E"
Range("E14").Value = Range("CR18").Value
End Select
End Sub

[/VBA]

[gimli]

Also..can a case be a fromula? Like an If/and? See below..thanks for all the help in advance.

[vba]
Sub Candyman1()
Select Case Range("BY15").Value
Case "A"
Range("E14").Value = Range("CG16").Value
Case "B"
Range("E14").Value = Range("CG17").Value
Case "C"
Range("E14").Value = Range("CR16").Value
Case "D"
Range("E14").Value = Range("CR17").Value
Case "E"
Range("E14").Value = Range("CR18").Value
Case ' IF(AND(F13>=24,BY15<>"E")
Range("E14").Value = "MESSAGE HERE"
End Select
End Sub

[/vba]

[austenr]

Perhaps a UDF?

[austenr]

You can put If's under a Case statement

[gimli]

UDF?

Ok so If statements cant go in a case statement.

My other question is looking at code below..when the value in cell BY15 changes, the value in E14 should change if it equals one of the case statments. This code doest cause a change unless I run the sub candyman. BY15 is linked to a drop down list..not sure if thats the problem. Think im explaining that right.

Sub Candyman1()
Select Case Range("BY15").Value
Case "A"
Range("E14").Value = Range("CG16").Value
Case "B"
Range("E14").Value = Range("CG17").Value
Case "C"
Range("E14").Value = Range("CR16").Value
Case "D"
Range("E14").Value = Range("CR17").Value

End Select
End Sub

[mdmackillop]

Hi Gimli,
These things are easier if you provide a small sample file.
Regards
MD

[gimli]

ok md..ill post tommorow

thanks much

[austenr]

Im thinking worksheet change event

[gimli]

Hi,

Ok..attached is a sample. I have a drop down box that changes the cell value but the sub candyman1 wont run when value is changed via drop down box.

If I type in the value it works... Can this be set up so the drop down selection fires the sub candyman1?

Also can I put mutible calls? See code....

thanks so much

[mdmackillop]

Use the events available to the combobox eg
[VBA]
Private Sub ComboBox1_Change()
Call Candyman1
End Sub

[/VBA]

[gimli]

All to easy

thanks much