IF Criteria Executing Code As If True When Should Be False

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,923
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
VBA Code:
Sub inf_groom(srow As Integer, pnum As String, fac2 As String, nrec As Long, st As Variant)
    Stop
    Dim d_cell As Range
    Debug.Print cd_rrow
    Debug.Print btype
    
    With ws_master
        Set d_cell = .Cells(srow, 8)
        mbevents = False
        .Unprotect
        
        'start assignment process
        'base crew
        b_label = .Cells(srow, 4) 'facility
        ws_thold.Range("Z1") = "=VLookup(""" & b_label & """,'D:\WSOP 2020\SupportData\[Facilities.xlsx]Facilities'!H:M,6,false)"
        pcrew_grm = ws_thold.Range("Z1").Value 'primary crew (1st option)
        
        'create a list of available crews to provide service

        cnt_b_label = Application.WorksheetFunction.CountIf(.Columns(4), b_label)
        If cnt_b_label > 1 Then 'there are multiple bookings at this facility. Is it eligible for grooming in relation to the others?
            Stop
        End If
        
        bkg_st = .Cells(srow, 6) 'booking start time
        bkg_et = .Cells(srow, 7) ' booking end time
        svc_off = bkg_st - TimeSerial(1, 0, 0) 'service offset 1 hour before booking start
        
        temp_grm_ct = 0
        thold_dr = 1
        For stp = 10 To 37 'step through schedule
            If .Cells(stp, 23).Value <> "Not Staffed" Or .Cells(stp, 23).Value <> "" Then  '<---- Not working
                temp_grm = .Cells(stp, 19) 'crew
                crew_st = .Cells(stp, 20) 'temp_grm start
                crew_et = .Cells(stp, 21) 'temp_grm end
                If svc_off > crew_st And svc_off < crew_et Then 'the shift can accomodate this service
                    temp_grm_cnt = temp_grm_ct + 1
                    ws_thold.Cells(thold_dr, 26) = tmp_grm
                End If
            End If
        Next stp
        
        d_cell = crew_grm
        .Protect
    End With
    mbevents = True
End Sub

Please refer to the line commented "<---- Not working" in the code's For STP loop.
With STP = 10, the value in W10 is "Not Staffed"

Perhaps I don't have the correct syntax, but since the value of ws_master.cells(10,23) = "Not Staffed", I don't know why the code is being triggered. I only want that code triggered when the value in .cells(stp,23) is anything but "Not Staffed" or empty.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,251
Office Version
  1. 365
Platform
  1. Windows
You need to change the Or to And
 
Solution

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,923
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hey Fluff!
Yep ... I get it now, and with that entlightenment came success! Thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,251
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,973
Messages
5,656,179
Members
418,287
Latest member
reba557

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