- Mar 23, 2004
- Office Version
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.