Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

When I run this code I get the error 'Next Without For' : Sub CalculateSsBooking() Dim…

  1. User

    When I run this code I get the error 'Next Without For' : Sub CalculateSsBooking() Dim wsTarget As Worksheet Dim rngSource As Range, rngTarget As Range, rngDelete As Range, cell As Range Set wsTarget = ThisWorkbook.Worksheets("SsBOOKonly") ' Clear contents of column V in target sheet wsTarget.Range("V:V").ClearContents Dim lastRow As Long Dim currentGroupStartRow As Long Dim currentDate As Date Dim latestTime As Date Dim maxTimeRow As Long lastRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).row ' Update the line with "wsTarget" ' Start from the second row currentGroupStartRow = 2 ' Loop through all rows For i = 2 To lastRow ' Check if the current date changes 'If wsTarget.Cells(i, 1).Value = "" Then Exit Sub ' Stop if empty cell is encountered If wsTarget.Cells(i, 1).Value = "" Then GoTo Transfer Else If wsTarget.Cells(i, 1).Value <> currentDate Then ' If it changes, calculate the latest time in the previous group latestTime = wsTarget.Cells(currentGroupStartRow, 5).Value maxTimeRow = currentGroupStartRow For j = currentGroupStartRow + 1 To i - 1 If wsTarget.Cells(j, 5).Value > latestTime Then latestTime = wsTarget.Cells(j, 5).Value maxTimeRow = j End If Next j ' Append "Close" to column V in the row with the latest time 'If wsTarget.Cells(i, 1).Value = "" Then Exit Sub ' Stop if empty cell is encountered If wsTarget.Cells(i, 1).Value = "" Then GoTo Transfer Else If maxTimeRow <= lastRow Then ' Check if maxTimeRow is within bounds wsTarget.Cells(maxTimeRow, 22).Value = "Close" End If ' Update variables for the next group currentDate = wsTarget.Cells(i, 1).Value currentGroupStartRow = i End If Next i Application.Wait (Now + timeValue("0:00:01")) Transfer: Call Module3.TransferFormat End Sub

  2. Assistant

    The error "Next Without For" occurs in this code because there is a missing "Next" statement to close the "For" loop before the "Next i" statement. To fix this error, add "Next" before the line "Next i".

    Updated code:

    For i = 2 To lastRow
        ' Check if the current date changes
        'If wsTarget.Cells(i, 1).Value = "" Then Exit Sub ' Stop if empty cell is encountered
        If wsTarget.Cells(i, 1).Value = "" Then
            GoTo Transfer
        Else
            If wsTarget.Cells(i, 1).Value <> currentDate Then
                ' If it changes, calculate the latest time in the previous group
                latestTime = wsTarget.Cells(currentGroupStartRow, 5).Value
                maxTimeRow = currentGroupStartRow
                For j = currentGroupStartRow + 1 To i - 1
                    If wsTarget.Cells(j, 5).Value > latestTime Then
                        latestTime = wsTarget.Cells(j, 5).Value
                        maxTimeRow = j
                    End If
                Next j
                ' Append "Close" to column V in the row with the latest time
                'If wsTarget.Cells(i, 1).Value = "" Then Exit Sub ' Stop if empty cell is encountered
                If wsTarget.Cells(i, 1).Value = "" Then
                    GoTo Transfer
                Else
                    If maxTimeRow <= lastRow Then ' Check if maxTimeRow is within bounds
                        wsTarget.Cells(maxTimeRow, 22).Value = "Close"
                    End If
                    ' Update variables for the next group
                    currentDate = wsTarget.Cells(i, 1).Value
                    currentGroupStartRow = i
                End If
            End If
        End If
    Next i
    

    Please note that I have also corrected the indentation in the code to improve readability.