Next Without For When Many If Statements

D

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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
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??
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Accidental duplicate caused by board error.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Have you stepped through the code?
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
156
kerm007
K
M
  • Question
VBA Code - Run Code if cell value is not blank
Replies
0
Views
431
MHamid
M
gtd526
  • Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
124
gtd526
gtd526
K
  • Solved
Update to existing Move data macro
2
Replies
11
Views
272
Alex Blakenburg
A
R
  • Question
Is There a More Efficient Way to Code This?
Replies
2
Views
347
offthelip
O

Forum statistics

Threads
1,214,784
Messages
6,121,539
Members
449,038
Latest member
Guest1337

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top