Next without For error using if statements

Question

I'm getting a compile error Next without For and I'm not sure why. Also this is my first attempt at this particular piece of code I need does anyone know if my **/01/** search would actually search for only the 'months' within a date field?

below is my code.

Private Sub LeaveDateCheck()

Dim wstSource As Worksheet, _
        wstOutput As Worksheet
    Dim rngCell As Range, _
        rngMyData As Range
    Dim lngMyRow As Long
    Dim Usdrws As Long, LastRow As Long


    'Call AddLeaveDateTable

    Usdrws = Worksheets("EeeDetails").Range("A" & Rows.Count).End(xlUp).Row

    Worksheets("EeeDetails").Range("AB2:AB" & Usdrws).FormulaR1C1 = "=iferror(vlookup(RC[-2],LeaveDate!C[-27]:C[-26],2,False),RC[-2])"

    Worksheets("EeeDetails").Range("AB:AB").NumberFormat = "dd/mm/yyyy"


    Set wstSource = Worksheets("EeeDetails") 'Tab containing source data. Change to suit.
    Set wstOutput = Worksheets("Errors") 'Tab for output (duplicate) data. Change to suit.
    Set rngMyData = wstSource.Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) 'Column range to check for duplicates. Change to suit.

    Application.ScreenUpdating = False

    For Each rngCell In rngMyData
        rownumber = Split(rngCell.Address, "$")(2)
        LeaveDate = Worksheets("EeeDetails").Range("Y" & rownumber).Value
        LastPayDate = Worksheets("EeeDetails").Range("AB" & rownumber).Value

        lngMyRow = wstOutput.Cells(Rows.Count, "A").End(xlUp).Row + 1

            If LeaveDate <> "" Then
                If LastPayDate <> "" Then
                    If LastPayDate <> "**/**/**" Then
                        If LastPayDate = "Wk 5" Then
                            If LeaveDate = "**/04/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/05/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                        If LastPayDate = "Wk 9" Then
                            If LeaveDate = "**/05/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/06/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                        If LastPayDate = "Wk 13" Then
                            If LeaveDate = "**/06/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/07/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                        If LastPayDate = "Wk 14" Then
                            If LeaveDate = "**/06/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/07/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                        If LastPayDate = "Wk 18" Then
                            If LeaveDate = "**/07/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/08/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                        If LastPayDate = "Wk 22" Then
                            If LeaveDate = "**/08/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/09/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                        If LastPayDate = "Wk 26" Then
                            If LeaveDate = "**/09/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/10/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                        If LastPayDate = "Wk 27" Then
                            If LeaveDate = "**/09/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/10/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                        If LastPayDate = "Wk 31" Then
                            If LeaveDate = "**/10/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/11/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                        If LastPayDate = "Wk 35" Then
                            If LeaveDate = "**/11/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/12/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                        If LastPayDate = "Wk 39" Then
                            If LeaveDate = "**/12/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/01/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                        If LastPayDate = "Wk 40" Then
                            If LeaveDate = "**/12/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/01/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                        If LastPayDate = "Wk 44" Then
                            If LeaveDate = "**/01/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/02/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                        If LastPayDate = "Wk 48" Then
                            If LeaveDate = "**/02/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                            End If
                            If LeaveDate = "**/03/**" Then
                                Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                                Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                                wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                                 Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                        End If
                    End If
                    If CDate(LeaveDate) > CDate(LastPayDate) Then
                        Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
                        Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
                        wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
                         Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
                    End If
                End If
            End If

    Next rngCell

End Sub

Also I'm sure this code could be condensed down and I will look at doing that later, just trying to get it to work at the moment!

Shai Rado · Accepted Answer · 2018-01-14 14:34:59Z

It's a bit long and hard to write in a comment, but basically you are missing 1 End If evey couple of Ifs.

For instance, if you indent your code properly:

If LastPayDate = "Wk 5" Then
    If LeaveDate = "**/04/**" Then
        Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
        Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
        wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
         Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
    End If
    If LeaveDate = "**/05/**" Then
        Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
        Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
        wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
         Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
    End If
    ' <--- MISSING End If HERE

    If LastPayDate = "Wk 9" Then
        If LeaveDate = "**/05/**" Then
            Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
            Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
            wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
             Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
        End If
        If LeaveDate = "**/06/**" Then
            Worksheets("EeeDetails").Range(LeaveDate, LastPayDate).Interior.ColorIndex = 3
            Worksheets("EeeDetails").Range("AA" & rownumber).Value = rownumber
            wstSource.Range("A" & rngCell.Row & ":AA" & rngCell.Row).Copy _
             Destination:=wstOutput.Range("A" & lngMyRow & ":AA" & lngMyRow)
        End If
        ' <--- MISSING End If HERE

        ' and so on until the end of your code

Suggestion: try using Select Case LastPayDate, it will help you "clean" up your code a little.

Hi Shai Rado, I just spotted that and have corrected it, works fine now! thanks.

Collectives™ on Stack Overflow

Next without For error using if statements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related