How to error handle a Next without For Error?

Question

When trying to run the below code, a compile error of for without next or next without for is experienced. The error message keeps appearing in loops (once a for without next error, the next time a next without for error), making it difficult to spot where the error is.

How to check if an "end if" is missing or if there is an indentation error?

Please help!

    Sub DataCleaning()

    Dim ws As Worksheet
    Dim myValue As Variant
    Dim StringToFind As String
    Dim f, cell, cell1 As Range
    Dim LastCol, LastCol1 As Long
    Dim i, j, k, l As Integer
   
    Application.DisplayAlerts = False   'Optional
   
    For Each ws In Worksheets
        Select Case ws.Name
            'Include sheet names to keep on next line (with comma between)
            Case "VIE", "CA", "UK", "EU", "CHN", "JP", "AU", "NZ", "KR", "PH", "TH", "ID"
                ws.Cells.ClearFormats
            Case Else
                ws.Delete
        End Select
    Next ws

    Application.DisplayAlerts = True
    
    StringToFind = Application.InputBox("Input Batch Number:")
    
    For Each ws In Worksheets
        'myValue = InputBox("Input Batch Number:", ws, 1)
    
        ws.Activate
        ActiveSheet.Rows(4).Select

        Set cell = Selection.Find(what:="Batch " & StringToFind, After:=ActiveCell, _
            LookIn:=xlFormulas, lookat:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If cell Is Nothing Then 
            MsgBox "No Order"
        Else
            cell.Offset(0, -1).Select
            ColumnLetter = Split(Cells(1, ActiveCell.Column).Address, "$")(1)
            Range(Columns("B"), Columns(ColumnLetter)).EntireColumn.Delete
    
            LastCol = Cells(5, Columns.Count).End(xlToLeft).Column
    
    
            ws.Activate
            ActiveSheet.Rows(5).Select
            
            Set cell1 = Selection.Find(what:="<", After:=ActiveCell, _
                LookIn:=xlFormulas, lookat:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            cell1.Select
            ColumnLetter1 = Split(Cells(1, ActiveCell.Column).Address, "$")(1)
            Range(Cells(1, ColumnLetter1), Cells(1, LastCol)).EntireColumn.Delete
        
        

            Rows(1).EntireRow.Delete
            Range("A1") = "Batch"
            Range("A2") = "City"
            Range("A3") = "Number"
            Range("A4") = "Shipment"
        
        
            LastCol1 = Cells(4, Columns.Count).End(xlToLeft).Column
            With Range("B1")
                For j = 2 To LastCol1
                    Cells(1, j) = StringToFind
                Next j
            End With
        
            With Range("B2")
                For k = 2 To LastCol1
                    Cells(2, k) = ws.Name
                Next k
            End With
        
            With Range("B3")
                For l = 2 To LastCol1
                    Cells(3, l) = ""
                Next l
            End With
        
            Cells(4, LastCol1 + 1) = "Price"
       
        
            i = 1
        
            Do While Not IsEmpty(Cells(i, 1))
                SKUColumn = Cells(i, 1)
                If SKUColumn Like "2018" Then
                    ws.Rows([i]).EntireRow.Delete
                    Deleted = True
                ElseIf SKUColumn Like "2020" Then
                    ws.Rows([i]).EntireRow.Delete
                    Deleted = True
                ElseIf SKUColumn Like "Accessories" Then
                    ws.Rows([i]).EntireRow.Delete
                    Deleted = True

                End If
                i = i + 1
            Loop
            
            Application.ScreenUpdating = True

            ws.Copy


            ActiveWorkbook.SaveAs Filename:= _
                "C:\Users\xxx\Desktop\" & ws.Name & ".csv" _
                , FileFormat:=xlCSV, CreateBackup:=False

            ActiveWorkbook.Close SaveChanges:=True

            Application.ScreenUpdating = False

    Next ws
               
        End If 'this line should be in front of `Next ws`

Indent your code correctly. The code in the `Else`-Branch of `If cell Is Nothing Then` is not indented. Correct Indentation helps you to find such errors. And read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba and clean up your code — FunThomas, Dec 01 '20 at 11:43
I edited your code in terms of indentation. Now it should be very clear (for everybody) what the previous two comments want saying... — FaneDuru, Dec 01 '20 at 11:47

How to error handle a Next without For Error?

0 Answers0