Task is to read through data in column to determine the start time and end time of shifts which vary.
If the first shift has a value less than 12 the shift start time is the beginning of the next shift ie Shift of 2 hours starts at 20:00 as the next shift starts at 24:00.
Most shifts are 12 hours that follow each other however if there is break such as shift hour order like, 2hr, 12hr, 12hr, 4hr, 12hr, 12hr the 4 hour shift starts at again 4 hours before the start of the next shift.
My Code so far is as allows, I feel I have stuffed up If statements
Sub RosterExceptions2()
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Long, j As Long
Dim Shiftduration As Double
Dim ShiftStart As Double
Dim shiftplus As Double
Dim currentshift As Double
Dim ShiftEnd As Double
Dim Rostertype As String
Set wb = ThisWorkbook
Set ws = wb.Sheets("XACT RE")
Set ws2 = wb.Sheets("TESTsheet")
For i = ws2.Cells(2, 4).Value To ws2.Cells(2, 6).Value
For j = 26 To 26
If ws.Cells(i, j).Value <> "" Then
Shiftduration = ws.Cells(i, j).Value * (0.5 / 12)
Rostertype = ws.Cells(3, 26)
ws2.Cells((i - ws2.Cells(2, 4).Value + 7), 101) = Rostertype
' Cell 3,5 is test sheet name title
nextshift = ws.Cells(i + 1, 2).Value
currentshift = ws.Cells(i, 2).Value
shiftplus = ws.Cells(i + 1, j).Value
ElseIf ws.Cells(i, j).Value < 12 Then
ShiftstartRange = nextshift - Shiftduration
ws2.Cells((i - ws2.Cells(2, 4).Value + 7), 99) = ShiftstartRange
ShiftEnd = ShiftstartRange + Shiftduration
ws2.Cells((i - ws2.Cells(2, 4).Value + 7), 100) = ShiftEnd
ElseIf ws.Cells(i, j).Value < 12 And IsEmpty(shiftplus) Then
ShiftstartRange = currentshift
ws2.Cells((i - ws2.Cells(2, 4).Value + 7), 99) = ShiftstartRange
ShiftEnd = ShiftstartRange + Shiftduration
ws2.Cells((i - ws2.Cells(2, 4).Value + 7), 100) = ShiftEnd
ElseIf ws.Cells(i, j).Value = 12 Then
ShiftstartRange = nextshift - Shiftduration
ShiftEnd = ShiftstartRange + Shiftduration
ws2.Cells((i - ws2.Cells(2, 4).Value + 7), 100) = ShiftEnd
End If
Else
End If
Next j
Next i
End Sub
Aucun commentaire:
Enregistrer un commentaire