menschmaschine
New Member
- Joined
- Dec 21, 2022
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
Can anyone tell me what I'm doing wrong here?
This is for a work scheduling sheet. Column A has "Shift 1", "Shift 2", and "Shift 3" in that order for various "Locations" down the sheet. So there may be 10 or so sets of "Shift 1", "Shift 2", and "Shift 3", with a variable number of rows in between (for the employees in those shifts). Column B has a Location Name, "Employee" header, and a list of employees at each location and for each shift. Then columns D through AE (4 through 31) have start and end times for each day in a 14-day period (so, 28 columns). The issue I'm trying to resolve is that there are some people that work 2 different shifts for a given location. But their start and end times for both shifts show up in both shifts. So, if someone worked Shift 1 and 2, I'm trying to get their Shift 2 times out of Shift 1 and vice versa. There are also employees that are off on a given day with no times in the cells, but the cells for those off days have a string that contains a ".", hence the InStr code below... it's just a way to leave the non-time cells alone.
So, I'm trying to get the code to look through the start times of the 14-day period (every other column, so... 4, 6, 8, 10, etc.) and, only for cells with time values, clear the contents of any start times (and its associated end time cell to the right) that don't meet the value criteria for that shift. I've tried this a number of ways, and below is my last attempt that I thought was close. But I'm getting a Type Mismatch error in the second shift code (don't know why it doesn't error in the 1st shift, but that isn't working either).
This is for a work scheduling sheet. Column A has "Shift 1", "Shift 2", and "Shift 3" in that order for various "Locations" down the sheet. So there may be 10 or so sets of "Shift 1", "Shift 2", and "Shift 3", with a variable number of rows in between (for the employees in those shifts). Column B has a Location Name, "Employee" header, and a list of employees at each location and for each shift. Then columns D through AE (4 through 31) have start and end times for each day in a 14-day period (so, 28 columns). The issue I'm trying to resolve is that there are some people that work 2 different shifts for a given location. But their start and end times for both shifts show up in both shifts. So, if someone worked Shift 1 and 2, I'm trying to get their Shift 2 times out of Shift 1 and vice versa. There are also employees that are off on a given day with no times in the cells, but the cells for those off days have a string that contains a ".", hence the InStr code below... it's just a way to leave the non-time cells alone.
So, I'm trying to get the code to look through the start times of the 14-day period (every other column, so... 4, 6, 8, 10, etc.) and, only for cells with time values, clear the contents of any start times (and its associated end time cell to the right) that don't meet the value criteria for that shift. I've tried this a number of ways, and below is my last attempt that I thought was close. But I'm getting a Type Mismatch error in the second shift code (don't know why it doesn't error in the 1st shift, but that isn't working either).
VBA Code:
Sub TimeCleanup_Test()
Dim USch As Worksheet, ELRow As Long, UTim As Range, UTimCel As Range
Dim ShCol As Range, ShCel As Range, ShLRow As Long, Sh1FRow As Long, Sh1LRow As Long, Sh2FRow As Long, Sh2LRow As Long, Sh3FRow As Long, Sh3LRow As Long
Dim UTim1 As Range, UTim1Cel As Range, UTim2 As Range, UTim2Cel As Range, UTim3 As Range, UTim3Cel As Range
Dim i As Integer
Application.ScreenUpdating = False
Set USch = Sheets("Unit Schedule")
ELRow = USch.Range("B" & Rows.Count).End(xlUp).Row
With USch
ShLRow = USch.Range("A" & Rows.Count).End(xlUp).Row
Set ShCol = USch.Range("A13:A" & ShLRow)
For Each ShCel In ShCol
If ShCel.value = "Shift 1" Then
Sh1FRow = ShCel.Offset(1, 0).Row
ElseIf ShCel.value = "Shift 2" Then
Sh1LRow = ShCel.Offset(-1, 0).Row
Sh2FRow = ShCel.Offset(1, 0).Row
ElseIf ShCel.value = "Shift 3" Then
Sh2LRow = ShCel.Offset(-1, 0).Row
Sh3FRow = ShCel.Offset(1, 0).Row
ElseIf ShCel.value = "^" Then
Sh3LRow = ShCel.Offset(-1, 0).Row
Set UTim1 = Range(Cells(Sh1FRow, 4), Cells(Sh1LRow, 31))
Set UTim2 = Range(Cells(Sh2FRow, 4), Cells(Sh2LRow, 31))
Set UTim3 = Range(Cells(Sh3FRow, 4), Cells(Sh3LRow, 31))
For i = 4 To 30 Step 2
Set UTim1 = UTim1.Columns(i)
For Each UTim1Cel In UTim1
If InStr(1, UTim1Cel.value, ".") = 0 Then
If UTim1Cel.value < 0.125 Or UTim1Cel.value > 0.457639 Then
UTim1Cel.ClearContents
UTim1Cel.Offset(0, 1).ClearContents
End If
End If
Next UTim1Cel
Set UTim2 = UTim2.Columns(i)
For Each UTim2Cel In UTim2
If InStr(1, UTim2Cel.value, ".") = 0 Then
If UTim2Cel.value < 0.458333 Or UTim2Cel.value > 0.791667 Then
UTim2Cel.ClearContents
UTim2Cel.Offset(0, 1).ClearContents
End If
End If
Next UTim2Cel
Set UTim3 = UTim3.Columns(i)
For Each UTim3Cel In UTim3
If InStr(1, UTim3Cel.value, ".") = 0 Then
If UTim3Cel.value > 0.124306 And UTim3Cel.value < 0.792361 Then
UTim3Cel.ClearContents
UTim3Cel.Offset(0, 1).ClearContents
End If
End If
Next UTim3Cel
Next i
End If
Next ShCel
End With
Application.ScreenUpdating = True
End Sub