#### dublinexceluser

##### New Member

- Joined
- Jun 17, 2013

- Messages
- 5

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