21 IF's & 51 AND's

[skull_eagle]

Hi Everyone,

I have written a formula containing 21 IF statements with 51 AND statements within.

I'm running a loop to to apply this formula to each row in a defined area, my problem is VBA won't let me run the formula over multiple rows. I'm using the " _" to try and get it to read the formula as a single line and it ain't working.

I keep getting Syntax errors or expected end statement errors.

This is an example:

ActiveCell.FormulaR1C1 = "=IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]<=TIME(16,0,0), RC[-12]>TIME(10,30,0), RC[-2]>TIME(16,0,0)), (TIME(0,30,0))+(RC[-2]-TIME(16,0,0)), IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]>TIME(10,30,0), RC[-12]<=TIME(16,0,0), RC[-2]<=TIME(16,0,0)), TIME(0,15,0), IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]<=TIME(10,30,0), RC[-2]<=TIME(10,30,0)), TIME(0,15,0), IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]<=TIME(10,30,0), RC[-2]>TIME(10,30,0)), (TIME(0,30,0))+(RC[-2]-TIME(10,30,0)), IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]>TIME(16,0,0), RC[-2]<=TIME(10,30,0)), TIME(0,15,0), IF(AND(RC[-15]=""Off-site"", RC[-14]=""Urgent"", RC[-13]=""Sat/Sun""), RC[-2]-TIME(8,0,0), IF(AND(RC[-15]=""Off-site"", RC[-14]=""Urgent"", RC[-1]=0, RC[-12]>=TIME(8,0,0)), RC[-2]-RC[-12], IF(AND(RC[-15]=""Off-site"", RC[-14]=""Urgent"",.......etc etc etc

I would prefer to make this look more like:

ActiveCell.FormulaR1C1 = _
"= IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]<=TIME(16,0,0), RC[-12]>TIME(10,30,0), RC[-2]>TIME(16,0,0)), (TIME(0,30,0))+(RC[-2]-TIME(16,0,0)), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]>TIME(10,30,0), RC[-12]<=TIME(16,0,0), RC[-2]<=TIME(16,0,0)), TIME(0,15,0), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]<=TIME(10,30,0), RC[-2]<=TIME(10,30,0)), TIME(0,15,0), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]<=TIME(10,30,0), RC[-2]>TIME(10,30,0)), (TIME(0,30,0))+(RC[-2]-TIME(10,30,0)), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]>TIME(16,0,0), RC[-2]<=TIME(10,30,0)), TIME(0,15,0), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Urgent"", RC[-13]=""Sat/Sun""), RC[-2]-TIME(8,0,0), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Urgent"", RC[-1]=0, RC[-12]>=TIME(8,0,0)), RC[-2]-RC[-12], _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Urgent"", RC[-1]=0, RC[-12]<TIME(8,0,0)), (RC[-2]-RC[-12])+(TIME(8,0,0)-RC[-12]), p _<> ' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Urgent"", RC[-1]>=1, RC[-12]<=TIME(18,0,0)), (TIME(18,0,0)-RC[-12])+(RC[-2]-TIME(8,0,0)), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Urgent"", RC[-1]>=1, RC[-12]>TIME(18,0,0)), RC[-2]-TIME(8,0,0), """"))))))))))))"
...........etc etc etc


I'm only new to VBA and I find it so much easier to bash all these if's and and's into a formula than messing around with arrays etc.

I'm really hoping someone can help me and Excel VBA actually has this capablitiy.

Thanks

 

[Norie]

I think you should take a look at the formula before anything else.
I've only had a quick look but I'm pretty sure you need all those ANDs.

 

[ukmikeb]

Hi

Break the formula down into more manageable chunks and then try concatenating those chunks.

If that doesn't work a further option would be to create a formula which is correct syntactically but created with "coded names" which you subsequently convert with a series of REPLACE or SUBSTITUTE statements.

And a further option would be to use helper columns containing parts of your formula to achieve the end result.

Good luck.

 

[JackDanIce]

Have a look at CASE statements

 

[jasonb75]

Hi Everyone,

I have written a formula containing 21 IF statements with 51 AND statements within.

I'm running a loop to to apply this formula to each row in a defined area, my problem is VBA won't let me run the formula over multiple rows. I'm using the " _" to try and get it to read the formula as a single line and it ain't working.

I keep getting Syntax errors or expected end statement errors.

ActiveCell.FormulaR1C1 = _
"= IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]<=TIME(16,0,0), RC[-12]>TIME(10,30,0), RC[-2]>TIME(16,0,0)), (TIME(0,30,0))+(RC[-2]-TIME(16,0,0)), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]>TIME(10,30,0), RC[-12]<=TIME(16,0,0), RC[-2]<=TIME(16,0,0)), TIME(0,15,0), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]<=TIME(10,30,0), RC[-2]<=TIME(10,30,0)), TIME(0,15,0), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]<=TIME(10,30,0), RC[-2]>TIME(10,30,0)), (TIME(0,30,0))+(RC[-2]-TIME(10,30,0)), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]>TIME(16,0,0), RC[-2]<=TIME(10,30,0)), TIME(0,15,0), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Urgent"", RC[-13]=""Sat/Sun""), RC[-2]-TIME(8,0,0), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Urgent"", RC[-1]=0, RC[-12]>=TIME(8,0,0)), RC[-2]-RC[-12], _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Urgent"", RC[-1]=0, RC[-12]<TIME(8,0,0)), _< p (RC[-2]-RC[-12])+(TIME(8,0,0)-RC[-12]),> ' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Urgent"", RC[-1]>=1, RC[-12]<=TIME(18,0,0)), (TIME(18,0,0)-RC[-12])+(RC[-2]-TIME(8,0,0)), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Urgent"", RC[-1]>=1, RC[-12]>TIME(18,0,0)), RC[-2]-TIME(8,0,0), """"))))))))))))"
...........etc etc etc


I'm only new to VBA and I find it so much easier to bash all these if's and and's into a formula than messing around with arrays etc.

I'm really hoping someone can help me and Excel VBA actually has this capablitiy.

Thanks

You have to close the double quotes and concatenate, i.e.

Activecell.FormulaR1C1 = "=Some formula" & _
     "more formula" & _
     "rest of formula"

You could probably shorten the formula significantly if you thought about your logic.

I haven't checked syntax fully, this is just a quick edit in the reply window, but for example

' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]>TIME(10,30,0), RC[-12]<=TIME(16,0,0), RC[-2]<=TIME(16,0,0)), TIME(0,15,0), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]<=TIME(10,30,0), RC[-2]<=TIME(10,30,0)), TIME(0,15,0), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]<=TIME(10,30,0), RC[-2]>TIME(10,30,0)), (TIME(0,30,0))+(RC[-2]-TIME(10,30,0)), _
' IF(AND(RC[-15]=""Off-site"", RC[-14]=""Standard"", RC[-12]>TIME(16,0,0), RC[-2]<=TIME(10,30,0)), TIME(0,15,0), _

Could be shortened to

' IF(OR(AND(RC[-15]=""Off-site"", RC[-14]=""Standard""),IF(AND( RC[-12]=MEDIAN(RC[-12],""10:30:01"",""16:00""),RC[-2]<=""16:00""), _
' MAX(RC[-12], RC[-2])<="10:30"),AND(RC[-12]>""16:00"", RC[-2]<=""10:30"")), ""00:15"", _
' IF(AND( RC[-12]<=""10:30""), RC[-2]>""10:30"")), (""0:30"")+(RC[-2]-""10:30""), _

While still maintaining the functionality of your original formula.

 

[JackDanIce]

I don't see why you can't do something like:

If activecell.offset(0,-15) = "Off-Site" Then
    If Activecell.Offset(0,-14) = "Standard" Then
     'do some stuff
    ElseIf Activecell.Offset(0,-14) = "Urgent" Then
     ' do some other stuff (within boundaries of the law)
   End if
End If

Be much easier to read than lots of ANDs and ORs, but that's just one (my) approach - lots of otherways of course

 

[skull_eagle]

Wow, Thank you so much everyone. So many options to choose from.

You've helped me no end!