Why doesn't this If And Or statement work?

brent.fraser

New Member
Joined
Aug 27, 2009
Messages
41
Hi all,

For the life of me I cannot figure out why this isn't working:
Code:
With Worksheets("Service Order Themes")
t = 0
For f = 2 To themeLastRow
If Worksheets("Service Order Themes").Range("A" & f).Value = "From/To" And _
Worksheets("Service Order Themes").Range("H" & f).Value = "Open" Or _
Worksheets("Service Order Themes").Range("H" & f).Value = "Assigned" Or _
Worksheets("Service Order Themes").Range("H" & f).Value = "Unassigned" Then
t = t + 1
End If
End With

basically it is incrementing t if the or statements are true and does not consider the first "From/To" value..... but that is the first part of the statement.

I even put a msgbox Worksheets("Service Order Themes").Range("A" & f).Value just before the t = t + 1 and it shows values that ARE NOT "From/To."

I found a work-around where I use 3 if statements (one for each "or") and it works.... what am I missing?

Thanks
B.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,976
You're missing a couple of things. First, the reason behind the With is so that you don't have to repeat the object on every line:

Rich (BB code):
With Worksheets("Service Order Themes")
    t = 0
    For f = 2 To themeLastRow
        If .Range("A" & f).Value = "From/To" And _
           .Range("H" & f).Value = "Open" Or _
           .Range("H" & f).Value = "Assigned" Or _
           .Range("H" & f).Value = "Unassigned" Then
            t = t + 1
        End If
    Next f
End With

You can just use the "." and the object defined in the With is implied.
I also added the Next f line.

Next, when doing mixed Ands and Ors, it's usually very helpful to use parentheses to separate the parts, otherwise it might not parse the way you expect:

Rich (BB code):
With Worksheets("Service Order Themes")
    t = 0
    For f = 2 To themeLastRow
        If .Range("A" & f).Value = "From/To" And _
           (.Range("H" & f).Value = "Open" Or _
           .Range("H" & f).Value = "Assigned" Or _
           .Range("H" & f).Value = "Unassigned") Then
            t = t + 1
        End If
    Next f
End With
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,299
Office Version
  1. 365
Platform
  1. Windows
I think you need brackets around the Or part.
Code:
If Worksheets("Service Order Themes").Range("A" & f).Value = "From/To" And _
(Worksheets("Service Order Themes").Range("H" & f).Value = "Open" Or _
Worksheets("Service Order Themes").Range("H" & f).Value = "Assigned" Or _
Worksheets("Service Order Themes").Range("H" & f).Value = "Unassigned") Then
 

brent.fraser

New Member
Joined
Aug 27, 2009
Messages
41
That nailed it with the "(" and that was perfect. I have the same type of logic in another place and it is working.... strange but thanks Eric.
 

brent.fraser

New Member
Joined
Aug 27, 2009
Messages
41
I think you need brackets around the Or part.
Code:
If Worksheets("Service Order Themes").Range("A" & f).Value = "From/To" And _
(Worksheets("Service Order Themes").Range("H" & f).Value = "Open" Or _
Worksheets("Service Order Themes").Range("H" & f).Value = "Assigned" Or _
Worksheets("Service Order Themes").Range("H" & f).Value = "Unassigned") Then

Thank you Norie.... as pointed out, the "( and )" solved it.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,538
Messages
5,659,384
Members
418,500
Latest member
Guru Prasad S

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top