lundi 11 janvier 2016

Excel VBA Loop Start and End Times

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