# Next Without For When Many If Statements

#### dublinexceluser

##### New Member
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

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
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
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
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
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
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
Accidental duplicate caused by board error.

Last edited:

#### dmt32

##### Well-known Member
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
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
Have you stepped through the code?

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...