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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.