PDA

View Full Version : Solved: IF Statement Limit Reached



gimli
04-22-2010, 08:22 AM
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
04-22-2010, 08:29 AM
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). :bow:

austenr
04-22-2010, 08:32 AM
You might want to consider Select Case as in this example:


Select Case < Expression to test> Case Do something
Case Else Do something else
End Select

Also give this link a look

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

mdmackillop
04-22-2010, 08:48 AM
Have a look here (http://www.cpearson.com/excel/nested.htm)

austenr
04-22-2010, 10:02 AM
Forgot about ole Chip Malcomb. :hi:

gimli
04-22-2010, 10:30 AM
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.


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

gimli
04-22-2010, 11:56 AM
Also..can a case be a fromula? Like an If/and? See below..thanks for all the help in advance.


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

austenr
04-22-2010, 12:26 PM
Perhaps a UDF?

austenr
04-22-2010, 12:33 PM
You can put If's under a Case statement

gimli
04-22-2010, 04:23 PM
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
04-22-2010, 04:37 PM
Hi Gimli,
These things are easier if you provide a small sample file.
Regards
MD

gimli
04-22-2010, 04:50 PM
ok md..ill post tommorow

thanks much

austenr
04-22-2010, 09:29 PM
Im thinking worksheet change event

gimli
04-23-2010, 06:52 AM
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
04-23-2010, 07:51 AM
Use the events available to the combobox eg

Private Sub ComboBox1_Change()
Call Candyman1
End Sub

gimli
04-23-2010, 08:03 AM
All to easy :beerchug:

thanks much