VBA If Statement not catching all the conditions that match

Lucky11

New Member
Joined
Oct 5, 2013
Messages
10
I am writing a VBA code to double check and make sure I don't double book instructors or classrooms. My code is below.

For each WeekDay/Period combination it checks the schedule and every time a T or R etc and 1 or 2 etc match up with the WeekDay/Period combination I should get a 1. The function does exactly what I want it to for every case except for the second part of a double(i.e. R when LabDay is "TR" or 2 for LabPeriod is "12") I've tried
Code:
Right(LabPeriod,1) = Period
but I don't get a 1 when I should. I do get a 1 for the first part (i.e. T when LabDay is "TR" or 1 for LabPeriod is "12"). The WeekDay/Period combinations are in separate cells so the fact that it catches the left value shouldn't have an impact on it catching the right value.

I have tried setting up variables and breaking the code to try and see if there is a format mismatch somewhere. (i.e. a number being compared to a string) but I haven't been able to find the problem. I'm relatively new to VBA so I'm hoping another set of more experienced eyes will be able to see the problem easily.
:oops:
Any help would be appreciated.
Lucky

Possible inputs:
ClassDay1,2,3 or WeekDay could be "M","T","W","R","F"
LabDay could be "T","R","TR","WF"
ClassPeriod or Period could be "1","2","3","4","5","6"
LabPeriod could be "12","34","56"

Code:
Function PeriodCalc(ClassDay1, ClassDay2, ClassDay3, LabDay, WeekDay, ClassPeriod, LabPeriod, Period)
    If ClassDay1 = WeekDay Or ClassDay2 = WeekDay Or ClassDay3 = WeekDay Or Left(LabDay, 1) = WeekDay Or Right(LabDay, 1) = WeekDay Then
        If ClassPeriod = Period Or Left(LabPeriod, 1) = Period Or Right(LabPeriod, 1) = Period Then
            PeriodCalc = 1
        End If
    Else
        PeriodCalc = 0
    End If
End Function
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Could be a data type problem, try this
Code:
CLng(Right(LabPeriod,1)) = Period
 

Lucky11

New Member
Joined
Oct 5, 2013
Messages
10
JLGWhiz - It was a data type problem. Period and LabPeriod are both numbers. I figured out that Right() and Left() output strings so to fix the issue I ended up using Val(). Thanks for your help.
Code:
Val(Right(LabPeriod, 1)) = Period[Code]
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Glad you solved it.
Regards, JLG
 

Forum statistics

Threads
1,137,151
Messages
5,679,897
Members
419,861
Latest member
AceDaMace

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