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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top