Next Without For When Many If Statements

dublinexceluser

New Member
Joined
Jun 17, 2013
Messages
5
Hi,

I am looping through the different sheets in a workbook and filtering by different criteria depending on the value of two different strings. There are 36 possible combinations of these string values so I need many If..ElseIf statements. However, when I have the full code I get an error message saying "Next Without For" but when I comment out a large section of the If statements and test then the code runs okay. The code is below; if I comment out a lot of the if statements with "If ConMth=" etc. then it works; I only get the 'Next without For' error when they're all there;

Has anyone else experienced this? Would really appreciate any help.

Thanks,




Sub MySub2()
Dim ws As Worksheet
Dim row As Long
Dim FirstMonth As Integer
Dim SecondMonth As Integer
Dim ConMth As String
Dim PrevConMth As String
Dim NextRow As Range
row = 2
For Each ws In ActiveWorkbook.Worksheets
lr = ws.Cells(Rows.Count, 1).End(xlUp).row 'Find last data row on each sheet
If ws.Name <> "Continuous" Then
ConMth = ws.Range("A1").Value
ConMth = Left(ConMth, Len(ConMth) - 8)
ConMth = Right(ConMth, Len(ConMth) - 2)
PrevSheetIndex = ws.Index - 1
PrevConMth = Worksheets(PrevSheetIndex).Range("A1").Value
PrevConMth = Left(PrevConMth, Len(PrevConMth) - 5)
PrevConMth = Right(PrevConMth, Len(PrevConMth) - 2)

If ConMth = "F" And PrevConMth = "V" Then
FirstMonth = 10
SecondMonth = 12
ElseIf ConMth = "F" And PrevConMth = "X" Then
FirstMonth = 11
SecondMonth = 12
ElseIf ConMth = "F" And PrevConMth = "Z" Then
FirstMonth = 12
SecondMonth = 12
If ConMth = "G" And PrevConMth = "X" Then
FirstMonth = 11
SecondMonth = 1
ElseIf ConMth = "G" And PrevConMth = "Z" Then
FirstMonth = 12
SecondMonth = 1
ElseIf ConMth = "G" And PrevConMth = "F" Then
FirstMonth = 1
SecondMonth = 1
If ConMth = "H" And PrevConMth = "Z" Then
FirstMonth = 12
SecondMonth = 2
ElseIf ConMth = "H" And PrevConMth = "F" Then
FirstMonth = 1
SecondMonth = 2
ElseIf ConMth = "H" And PrevConMth = "G" Then
FirstMonth = 2
SecondMonth = 2
If ConMth = "J" And PrevConMth = "F" Then
FirstMonth = 1
SecondMonth = 3
ElseIf ConMth = "J" And PrevConMth = "G" Then
FirstMonth = 2
SecondMonth = 3
ElseIf ConMth = "J" And PrevConMth = "H" Then
FirstMonth = 3
SecondMonth = 3
If ConMth = "K" And PrevConMth = "G" Then
FirstMonth = 2
SecondMonth = 4
ElseIf ConMth = "K" And PrevConMth = "H" Then
FirstMonth = 3
SecondMonth = 4
ElseIf ConMth = "K" And PrevConMth = "J" Then
FirstMonth = 4
SecondMonth = 4
ElseIf ConMth = "M" And PrevConMth = "H" Then
FirstMonth = 3
SecondMonth = 5
ElseIf ConMth = "M" And PrevConMth = "J" Then
FirstMonth = 4
SecondMonth = 5
ElseIf ConMth = "M" And PrevConMth = "K" Then
FirstMonth = 5
SecondMonth = 5
If ConMth = "N" And PrevConMth = "J" Then
FirstMonth = 4
SecondMonth = 6
ElseIf ConMth = "N" And PrevConMth = "K" Then
FirstMonth = 5
SecondMonth = 6
ElseIf ConMth = "N" And PrevConMth = "M" Then
FirstMonth = 6
SecondMonth = 6
If ConMth = "Q" And PrevConMth = "K" Then
FirstMonth = 5
SecondMonth = 7
ElseIf ConMth = "Q" And PrevConMth = "M" Then
FirstMonth = 6
SecondMonth = 7
ElseIf ConMth = "Q" And PrevConMth = "N" Then
FirstMonth = 7
SecondMonth = 7
ElseIf ConMth = "U" And PrevConMth = "M" Then
FirstMonth = 6
SecondMonth = 8
ElseIf ConMth = "U" And PrevConMth = "N" Then
FirstMonth = 7
SecondMonth = 8
ElseIf ConMth = "U" And PrevConMth = "Q" Then
FirstMonth = 8
SecondMonth = 8
If ConMth = "V" And PrevConMth = "N" Then
FirstMonth = 7
SecondMonth = 9
ElseIf ConMth = "V" And PrevConMth = "Q" Then
FirstMonth = 8
SecondMonth = 9
ElseIf ConMth = "V" And PrevConMth = "U" Then
FirstMonth = 9
SecondMonth = 9
ElseIf ConMth = "X" And PrevConMth = "Q" Then
FirstMonth = 8
SecondMonth = 10
ElseIf ConMth = "X" And PrevConMth = "U" Then
FirstMonth = 9
SecondMonth = 10
ElseIf ConMth = "X" And PrevConMth = "V" Then
FirstMonth = 10
SecondMonth = 10
ElseIf ConMth = "Z" And PrevConMth = "U" Then
FirstMonth = 9
SecondMonth = 11
ElseIf ConMth = "Z" And PrevConMth = "V" Then
FirstMonth = 10
SecondMonth = 11
Else
FirstMonth = 11
SecondMonth = 11
End If
' ws.AutoFilterMode = False
'Filter entire range
' If ConMth = "H" And PrevConMth = "Z" Then
' ws.Range("A2:J" & lr).AutoFilter Field:=10, Criteria1:="<=" & SecondMonth, Operator:=xlOr, Criteria2:="=" & FirstMonth
' ElseIf ConMth = "G" And PrevConMth = "X" Then ' Possible problem here with greater to piece
'ws.Range("A2:J" & lr).AutoFilter Field:=10, Criteria1:="=" & SecondMonth, Operator:=xlOr, Criteria2:=">=" & FirstMonth
'ElseIf ConMth = "G" And PrevConMth = "Z" Then
'ws.Range("A2:J" & lr).AutoFilter Field:=10, Criteria1:="=" & SecondMonth, Operator:=xlOr, Criteria2:="=" & FirstMonth
'Else
'ws.Range("A2:J" & lr).AutoFilter Field:=10, Criteria1:=">=" & FirstMonth, Operator:=xlAnd, Criteria2:="<=" & SecondMonth
'End If
'With Worksheets("Continuous")
'Set NextRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
'End With
'ws.Range("A3:I" & lr).SpecialCells(xlCellTypeVisible).Copy NextRow

ws.Range("J1") = FirstMonth
ws.Range("K1") = SecondMonth

End If

Next ws

End Sub
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,641
You might prefer using a Select Case construct


Code:
Select Case ConMonth & PrevConMonth
    Case "FV"
         FirstMonth = 10
         SecondMonth = 12
    Case "FX" 
         FirstMonth = 11
         SecondMonth = 12
     Case "FZ"
         FirstMonth = 12
         SecondMonth = 12
 
'etc.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,660
Office Version
365
Platform
Windows
The problem is a whole bunch of missing End Ifs.

This will compile and run, but I'm pretty sure there's a better way to structure the If ElseIf Else If stuff.
Code:
Sub MySub2()
Dim ws As Worksheet
Dim row As Long
Dim FirstMonth As Integer
Dim SecondMonth As Integer
Dim ConMth As String
Dim PrevConMth As String
Dim NextRow As Range
    row = 2
    For Each ws In ActiveWorkbook.Worksheets
        lr = ws.Cells(Rows.Count, 1).End(xlUp).row    'Find last data row on each sheet
        If ws.Name <> "Continuous" Then
            ConMth = ws.Range("A1").Value
            ConMth = Left(ConMth, Len(ConMth) - 8)
            ConMth = Right(ConMth, Len(ConMth) - 2)
            PrevSheetIndex = ws.Index - 1
            PrevConMth = Worksheets(PrevSheetIndex).Range("A1").Value
            PrevConMth = Left(PrevConMth, Len(PrevConMth) - 5)
            PrevConMth = Right(PrevConMth, Len(PrevConMth) - 2)

            If ConMth = "F" And PrevConMth = "V" Then
                FirstMonth = 10
                SecondMonth = 12
            ElseIf ConMth = "F" And PrevConMth = "X" Then
                FirstMonth = 11
                SecondMonth = 12
            ElseIf ConMth = "F" And PrevConMth = "Z" Then
                FirstMonth = 12
                SecondMonth = 12
                If ConMth = "G" And PrevConMth = "X" Then
                    FirstMonth = 11
                    SecondMonth = 1
                ElseIf ConMth = "G" And PrevConMth = "Z" Then
                    FirstMonth = 12
                    SecondMonth = 1
                ElseIf ConMth = "G" And PrevConMth = "F" Then
                    FirstMonth = 1
                    SecondMonth = 1
                    If ConMth = "H" And PrevConMth = "Z" Then
                        FirstMonth = 12
                        SecondMonth = 2
                    ElseIf ConMth = "H" And PrevConMth = "F" Then
                        FirstMonth = 1
                        SecondMonth = 2
                    ElseIf ConMth = "H" And PrevConMth = "G" Then
                        FirstMonth = 2
                        SecondMonth = 2
                        If ConMth = "J" And PrevConMth = "F" Then
                            FirstMonth = 1
                            SecondMonth = 3
                        ElseIf ConMth = "J" And PrevConMth = "G" Then
                            FirstMonth = 2
                            SecondMonth = 3
                        ElseIf ConMth = "J" And PrevConMth = "H" Then
                            FirstMonth = 3
                            SecondMonth = 3
                            If ConMth = "K" And PrevConMth = "G" Then
                                FirstMonth = 2
                                SecondMonth = 4
                            ElseIf ConMth = "K" And PrevConMth = "H" Then
                                FirstMonth = 3
                                SecondMonth = 4
                            ElseIf ConMth = "K" And PrevConMth = "J" Then
                                FirstMonth = 4
                                SecondMonth = 4
                            ElseIf ConMth = "M" And PrevConMth = "H" Then
                                FirstMonth = 3
                                SecondMonth = 5
                            ElseIf ConMth = "M" And PrevConMth = "J" Then
                                FirstMonth = 4
                                SecondMonth = 5
                            ElseIf ConMth = "M" And PrevConMth = "K" Then
                                FirstMonth = 5
                                SecondMonth = 5
                                If ConMth = "N" And PrevConMth = "J" Then
                                    FirstMonth = 4
                                    SecondMonth = 6
                                ElseIf ConMth = "N" And PrevConMth = "K" Then
                                    FirstMonth = 5
                                    SecondMonth = 6
                                ElseIf ConMth = "N" And PrevConMth = "M" Then
                                    FirstMonth = 6
                                    SecondMonth = 6
                                    If ConMth = "Q" And PrevConMth = "K" Then
                                        FirstMonth = 5
                                        SecondMonth = 7
                                    ElseIf ConMth = "Q" And PrevConMth = "M" Then
                                        FirstMonth = 6
                                        SecondMonth = 7
                                    ElseIf ConMth = "Q" And PrevConMth = "N" Then
                                        FirstMonth = 7
                                        SecondMonth = 7
                                    ElseIf ConMth = "U" And PrevConMth = "M" Then
                                        FirstMonth = 6
                                        SecondMonth = 8
                                    ElseIf ConMth = "U" And PrevConMth = "N" Then
                                        FirstMonth = 7
                                        SecondMonth = 8
                                    ElseIf ConMth = "U" And PrevConMth = "Q" Then
                                        FirstMonth = 8
                                        SecondMonth = 8
                                        If ConMth = "V" And PrevConMth = "N" Then
                                            FirstMonth = 7
                                            SecondMonth = 9
                                        ElseIf ConMth = "V" And PrevConMth = "Q" Then
                                            FirstMonth = 8
                                            SecondMonth = 9
                                        ElseIf ConMth = "V" And PrevConMth = "U" Then
                                            FirstMonth = 9
                                            SecondMonth = 9
                                        ElseIf ConMth = "X" And PrevConMth = "Q" Then
                                            FirstMonth = 8
                                            SecondMonth = 10
                                        ElseIf ConMth = "X" And PrevConMth = "U" Then
                                            FirstMonth = 9
                                            SecondMonth = 10
                                        ElseIf ConMth = "X" And PrevConMth = "V" Then
                                            FirstMonth = 10
                                            SecondMonth = 10
                                        ElseIf ConMth = "Z" And PrevConMth = "U" Then
                                            FirstMonth = 9
                                            SecondMonth = 11
                                        ElseIf ConMth = "Z" And PrevConMth = "V" Then
                                            FirstMonth = 10
                                            SecondMonth = 11
                                        Else
                                            FirstMonth = 11
                                            SecondMonth = 11
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
            ' ws.AutoFilterMode = False
            'Filter entire range
            ' If ConMth = "H" And PrevConMth = "Z" Then
            ' ws.Range("A2:J" & lr).AutoFilter Field:=10, Criteria1:="<=" & SecondMonth, Operator:=xlOr, Criteria2:="=" & FirstMonth
            ' ElseIf ConMth = "G" And PrevConMth = "X" Then ' Possible problem here with greater to piece
            'ws.Range("A2:J" & lr).AutoFilter Field:=10, Criteria1:="=" & SecondMonth, Operator:=xlOr, Criteria2:=">=" & FirstMonth
            'ElseIf ConMth = "G" And PrevConMth = "Z" Then
            'ws.Range("A2:J" & lr).AutoFilter Field:=10, Criteria1:="=" & SecondMonth, Operator:=xlOr, Criteria2:="=" & FirstMonth
            'Else
            'ws.Range("A2:J" & lr).AutoFilter Field:=10, Criteria1:=">=" & FirstMonth, Operator:=xlAnd, Criteria2:="<=" & SecondMonth
            'End If
            'With Worksheets("Continuous")
            'Set NextRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
            'End With
            'ws.Range("A3:I" & lr).SpecialCells(xlCellTypeVisible).Copy NextRow

            ws.Range("J1") = FirstMonth
            ws.Range("K1") = SecondMonth

        End If

    Next ws

End Sub
 

dublinexceluser

New Member
Joined
Jun 17, 2013
Messages
5
Thanks; that code ran but amazingly returned values of 0 for both the FirstMonth and SecondMonth even though none of the conditions in the if statements give the possibility of a 0 value??
 

dublinexceluser

New Member
Joined
Jun 17, 2013
Messages
5
Okay; I tried to use the Select Case Construct as well but now I'm getting an "End If without block If" compile error; I think it comes back again to my incorrect construction of the last if statement that remains and the code inbetween but I've never had an issue with if statements before so I'm not sure how to fix; would be very grateful if someone could point out where I'm going wrong;

Thanks,

Code:
Sub MySub2()
 Dim ws As Worksheet
 Dim row As Long
 Dim FirstMonth As Integer
 Dim SecondMonth As Integer
 Dim ConMth As String
 Dim PrevConMth As String
 Dim NextRow As Range
 row = 2
 For Each ws In ActiveWorkbook.Worksheets
 lr = ws.Cells(Rows.Count, 1).End(xlUp).row 'Find last data row on each sheet
 If ws.Name <> "Continuous" Then
         ConMth = ws.Range("A1").Value
         ConMth = Left(ConMth, Len(ConMth) - 8)
         ConMth = Right(ConMth, Len(ConMth) - 2)
         PrevSheetIndex = ws.Index - 1
         PrevConMth = Worksheets(PrevSheetIndex).Range("A1").Value
         PrevConMth = Left(PrevConMth, Len(PrevConMth) - 5)
         PrevConMth = Right(PrevConMth, Len(PrevConMth) - 2)
         Select Case ConMonth & PrevConMonth
            Case "FV"
                 FirstMonth = 10
                 SecondMonth = 12
            Case "FX"
                 FirstMonth = 11
                 SecondMonth = 12
            Case "FZ"
                 FirstMonth = 12
                 SecondMonth = 12
            Case "GX"
                 FirstMonth = 11
                 SecondMonth = 1
            Case "GZ"
                 FirstMonth = 12
                 SecondMonth = 1
            Case "GF"
                 FirstMonth = 1
                 SecondMonth = 1
            Case "HZ"
                 FirstMonth = 12
                 SecondMonth = 2
            Case "HF"
                 FirstMonth = 1
                 SecondMonth = 2
            Case "HG"
                 FirstMonth = 2
                 SecondMonth = 2
            Case "JF"
                 FirstMonth = 1
                 SecondMonth = 3
            Case "JG"
                 FirstMonth = 2
                 SecondMonth = 3
            Case "JH"
                 FirstMonth = 3
                 SecondMonth = 3
            Case "KG"
                 FirstMonth = 2
                 SecondMonth = 4
            Case "KH"
                 FirstMonth = 3
                 SecondMonth = 4
            Case "KJ"
                 FirstMonth = 4
                 SecondMonth = 4
            Case "MH"
                 FirstMonth = 3
                 SecondMonth = 5
            Case "MJ"
                 FirstMonth = 4
                 SecondMonth = 5
            Case "MK"
                 FirstMonth = 5
                 SecondMonth = 5
            Case "NJ"
                 FirstMonth = 4
                 SecondMonth = 6
            Case "NK"
                 FirstMonth = 5
                 SecondMonth = 6
            Case "NM"
                 FirstMonth = 6
                 SecondMonth = 6
            Case "QK"
                 FirstMonth = 5
                 SecondMonth = 7
            Case "QM"
                 FirstMonth = 6
                 SecondMonth = 7
            Case "QN"
                 FirstMonth = 7
                 SecondMonth = 7
            Case "UM"
                 FirstMonth = 6
                 SecondMonth = 8
            Case "UN"
                 FirstMonth = 7
                 SecondMonth = 8
            Case "UQ"
                 FirstMonth = 8
                 SecondMonth = 8
            Case "VN"
                 FirstMonth = 7
                 SecondMonth = 9
            Case "VQ"
                 FirstMonth = 8
                 SecondMonth = 9
            Case "VU"
                 FirstMonth = 9
                 SecondMonth = 9
            Case "XQ"
                 FirstMonth = 8
                 SecondMonth = 10
            Case "XU"
                 FirstMonth = 9
                 SecondMonth = 10
            Case "XV"
                 FirstMonth = 10
                 SecondMonth = 10
            Case "ZU"
                 FirstMonth = 9
                 SecondMonth = 11
            Case "ZV"
                 FirstMonth = 10
                 SecondMonth = 11
            Case "ZX"
                 FirstMonth = 11
                 SecondMonth = 11
        
        ' ws.AutoFilterMode = False
         'Filter entire range
         '   If ConMth = "H" And PrevConMth = "Z" Then
          '  ws.Range("A2:J" & lr).AutoFilter Field:=10, Criteria1:="<=" & SecondMonth, Operator:=xlOr, Criteria2:="=" & FirstMonth
           ' ElseIf ConMth = "G" And PrevConMth = "X" Then ' Possible problem here with greater to piece
            'ws.Range("A2:J" & lr).AutoFilter Field:=10, Criteria1:="=" & SecondMonth, Operator:=xlOr, Criteria2:=">=" & FirstMonth
            'ElseIf ConMth = "G" And PrevConMth = "Z" Then
            'ws.Range("A2:J" & lr).AutoFilter Field:=10, Criteria1:="=" & SecondMonth, Operator:=xlOr, Criteria2:="=" & FirstMonth
            'Else
            'ws.Range("A2:J" & lr).AutoFilter Field:=10, Criteria1:=">=" & FirstMonth, Operator:=xlAnd, Criteria2:="<=" & SecondMonth
            'End If
        
         'With Worksheets("Continuous")
         'Set NextRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
         'End With
         'ws.Range("A3:I" & lr).SpecialCells(xlCellTypeVisible).Copy NextRow
         
         ws.Range("J1") = FirstMonth
         ws.Range("K1") = SecondMonth
 
 End If
 
 Next ws
 
 End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,660
Office Version
365
Platform
Windows
You should probably check what's actually happening by stepping through with F8 and checking what's in the various variables.

It would also be a good idea to not have all those If ElseIf End Ifs etc, try using a Select Case as Mike suggested.

That would make the code easier to follow and easier to debug/check.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,660
Office Version
365
Platform
Windows
Accidental duplicate caused by board error.
 
Last edited:

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,924
Office Version
2019
Platform
Windows
at the end of your select case construct you need to add End Select

Rich (BB code):
Case "ZX"
FirstMonth = 11
SecondMonth = 11
End Select
 

dublinexceluser

New Member
Joined
Jun 17, 2013
Messages
5
Many thanks for advice on End Select - that got the code working ( I had never used select case constructs before) - however it is still only returning values of 0 for the FirstMonth and SecondMonth even when they clearly shouldn't be zero; I have commented out large sections to try and find the issue but even when there are only a couple of the select case statements it still only returns 0 for each value.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,660
Office Version
365
Platform
Windows
Have you stepped through the code?
 

Watch MrExcel Video

Forum statistics

Threads
1,102,695
Messages
5,488,328
Members
407,634
Latest member
ps01

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top