Search code examples
arraysexcelvbaloopsrow

How to loop VBA row and not stopping at last row?

new to VBA and I am trying to calculate, for example, total hours from cells if we enter start and end month by input Box. I want to calculate sum of cells by looping through each row, my current program work if I enter startMonth < endMonth, but I want to make it work also if we input startMonth > endMonth. (e.g. from month 10 to 2, and calculate month 10+11+12+1+2)

Function TotalOvertime(startMonth As Integer, endMonth As Integer) As Integer

Dim i As Integer, j As Integer
Dim time As Integer, overtime As Integer

If (startMonth < 1 Or 12 < startMonth) Or (endMonth < 1 Or 12 < endMonth) Then
    TotalOvertime = -1
Else
    For i = startMonth + 1 To endMonth + 1
        For j = 2 To 5
            time = Cells(i, j).Value
            overtime = time - 40
            TotalOvertime = TotalOvertime + overtime
        Next j
    Next i
End If

End Function

Sub Kadai()

Dim startMonth As Integer, endMonth As Integer
Dim overtime As Integer

startMonth = InputBox("Enter starting month。")
endMonth = InputBox("Enter ending month。")
overtime = TotalOvertime(startMonth, endMonth)

If overtime <> -1 Then
    Call MsgBox(overtime)
Else
    Call MsgBox("Error")
End If

End Sub

Below is the data sample:

enter image description here

Current program works as: From 1st to 2nd month overtime = 40 hours. From 1st to 1st month overtime = 18 hours.

How should I loop so when I input start month is higher than end month it will iterate over the row? Thanks in advance.

Solution

  • Replace your:

    For i = startMonth + 1 To endMonth + 1
    For j = 2 To 5
        time = Cells(i, j).Value
        overtime = time - 40
        TotalOvertime = TotalOvertime + overtime
    Next j
Next i

    with:

    For i = 2 To 13
    If endMonth < startMonth Then
        For j = 2 To 5
            If Cells(i, 1).Value >= startMonth Or Cells(i, 1).Value <= endMonth Then
                time = Cells(i, j).Value
                overtime = time - 40
                TotalOvertime = TotalOvertime + overtime
            End If
        Next j
    Else
        For j = 2 To 5
            If Cells(i, 1).Value >= startMonth And Cells(i, 1).Value <= endMonth Then
                time = Cells(i, j).Value
                overtime = time - 40
                TotalOvertime = TotalOvertime + overtime
            End If
        Next j
    End If
Next i