If ... Or ... then with unexpected results

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
534
Office Version
  1. 2016
Platform
  1. Windows
I have a series of workbooks, one for each week with schedules on them. Each workbook is created from the same workbook with the only difference is the file name. Each workbook has a weekly sheet showing each day of the week and what times are booked.

Using a worksheet_SelectionChange event, I can switch to another workbook which is like switching to a different week's schedule.

During the weekly view activate event and a workbook close event, I take a picture of the weekly view worksheet to post on sharepoint, so all can see the most current schedule. (not everyone is allowed to update, they call the scheduler for that).

I only need the worksheets for "this week" and "Next week" to create the picture of the weekly view. (I may have 4-5 weeks open in the future). Through a VLOOKUP I set the value of CX9 to either "Current Week.jpg", "Next week.jpg", or NULL and use that value for the file name of the file for SharePoint.

The ExportToJPG routine has an IF OR statement:

xSheet = Range("CX9").Value

If xSheet = "Current Week.jpg" Or xSheet = "Next Week.jpg" Then
'{SAVE JPG}
Else
MsgBox "Not updating"
End If

It all works great for the Current week's workbook, but not for the Next weeks workbook. My goal is ... if CX9 is either "Current Week.jpg" or "Next week.jpg" to save the jpg file that's picked up in sharepoint. If CX9 is something else, go to the ELSE statement and display the message "Not updating" (to be removed when done).

I've stepped through the code, check the value in CX9 and it seems correct (no extra spaces, upper and lower case match)

What am I overlooking?

Mark
 

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.

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
358
Office Version
  1. 2016
Platform
  1. Windows
hmm, if im reading this correctly, u ran into what i find interesting today.
Try use AND operator instead if OR ;)
If this helps, im glad, if not then sry :(
 

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
534
Office Version
  1. 2016
Platform
  1. Windows
Thanks CsJHUN ... the AND didn't work (I had tried that before. In a continuous attempt to reach for straws, I put () around each side of the OR statement as in:

If (xSheet = "Current Week.jpg") Or (xSheet = "Next week.jpg") Then

is currently working. I'm still doing testing and I'll post if something comes up.

I didn't think that should work or be necessary. But, fingers crossed.
 

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
If xSheet = "Current Week.jpg" Or xSheet = "Next Week.jpg" Then
[....]
works great for the Current week's workbook, but not for the Next weeks workbook.
If (xSheet = "Current Week.jpg") Or (xSheet = "Next week.jpg") Then

is currently working.

Test carefully. The extra parentheses should not make a difference. To demonstrate:

Code:
Sub doit()
Dim x As Long, s As String
For x = 1 To 3
If x = 1 Or x = 2 Then
    s = s & vbNewLine & x & " okay"
Else
    s = s & vbNewLine & x & " not okay"
End If
Next
MsgBox Mid(s, 2)
End Sub

The result is:

1 okay
2 okay
3 not okay

As further confirmation, note that the operator precedence help page states:

``When expressions contain operators from more than one category, arithmetic operators are evaluated first, comparison operators are evaluated next, and logical operators are evaluated last.``
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,523
Messages
5,854,219
Members
431,627
Latest member
mantesh

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