Why doesn't this If And Or statement work?

[brent.fraser]

Hi all,

For the life of me I cannot figure out why this isn't working:

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.

 

[Eric W]

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:

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:

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

 

[Norie]

I think you need brackets around the Or part.

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]

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]

I think you need brackets around the Or part.

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.

 

[Eric W]

Glad we could help.