OR & AND in Macro

[Roderick_E]

Hi there,
I understand how to do embeded or/and functions in a formula but I guess I don't understand how to do it in a macro.

Example:
If Sheets("AIR SURCHARGE TEST1").Cells(i, 8) = Sheets("SURCHARGES (ACTIVE)").Cells(7, 19).Value OR AND Sheets("SURCHARGES (ACTIVE)").Cells(7, 2).Value ="all" AND Sheets("SURCHARGES (ACTIVE)").Cells(7, 3).Value = "all" AND Sheets("SURCHARGES (ACTIVE)").Cells(7, 4).Value = "all" AND Sheets("SURCHARGES (ACTIVE)").Cells(7, 5).Value = "all"

I'm going to want to build some more OR statements into this I have a 14 senarios. The above is just 2 possible (exact match OR if 4 columns on Surcharges (Active) all equal "all")

the compiler complains about syntax error, do I need ( ) somewhere???
Thanks for the help

 

[texasalynn]

at first glance I see
OR AND
together, no "(" could be the problem

 

[schielrn]

Maybe you want:

If Sheets("AIR SURCHARGE TEST1").Cells(i, 8) = Sheets("SURCHARGES (ACTIVE)").Cells(7, 19).Value OR (Sheets("SURCHARGES (ACTIVE)").Cells(7, 2).Value ="all" AND Sheets("SURCHARGES (ACTIVE)").Cells(7, 3).Value = "all" AND Sheets("SURCHARGES (ACTIVE)").Cells(7, 4).Value = "all" AND Sheets("SURCHARGES (ACTIVE)").Cells(7, 5).Value = "all")

Hope that helps.

 

[texasalynn]

this seems to work

If Sheets("AIR SURCHARGE TEST1").Cells(i, 8) = Sheets("SURCHARGES (ACTIVE)").Cells(7, 19).Value Or _
    (Sheets("SURCHARGES (ACTIVE)").Cells(7, 2).Value = "all") And Sheets("SURCHARGES (ACTIVE)").Cells(7, 3).Value = "all" _
    And Sheets("SURCHARGES (ACTIVE)").Cells(7, 4).Value = "all" _
    And Sheets("SURCHARGES (ACTIVE)").Cells(7, 5).Value = "all" Then

 

[Roderick_E]

this seems to work

If Sheets("AIR SURCHARGE TEST1").Cells(i, 8) = Sheets("SURCHARGES (ACTIVE)").Cells(7, 19).Value Or _
    (Sheets("SURCHARGES (ACTIVE)").Cells(7, 2).Value = "all") And Sheets("SURCHARGES (ACTIVE)").Cells(7, 3).Value = "all" _
    And Sheets("SURCHARGES (ACTIVE)").Cells(7, 4).Value = "all" _
    And Sheets("SURCHARGES (ACTIVE)").Cells(7, 5).Value = "all" Then

Ok let me test it...

 

[Roderick_E]

Hmm, is VBA case specific?? seems I need to check "all" & "ALL"???

 

[schielrn]

It depends on some of your options and if you declare option excplicit at the top of the code. TexasALynn may be missing a parentheses, did you try mine? Also you don't need to test for all and ALL you can do:

ucase(("SURCHARGES (ACTIVE)").Cells(7, 5).Value) = "ALL"

or:

lcase(("SURCHARGES (ACTIVE)").Cells(7, 5).Value) = "all"

Hope that helps.

 

[pgc01]

Hmm, is VBA case specific?? seems I need to check "all" & "ALL"???

Hi

If you don't want the text comparisons to be case sensitive add at the top of the module:

Option Compare Text