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
 
Sort by date Sort by votes
If you mean by pressing F8 all the eay through yes; it seems to compile and run okay but in all cases I get outputs of 0 for FirstMonth and SecondMonth which I should never get. Current code is as follows (you can see I've commented out a section because this section isn't the problem) -

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
            End Select
            
        
        ' 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
Both the ElseIf and Select Case approaches are case sensitive. To make it insensitive

Code: 
Select Case UCase(ConMonth & PrevConMonth)
            Case "FV"
                 FirstMonth = 10
                 SecondMonth = 12
            Case "FX"
                 FirstMonth = 11
                 SecondMonth = 12
' etc
End Select
 
Upvote 0
Did you check what was happening and the values of the variables as you stepped through?
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Solved
Rewrite Code as a Sub to work on Active Sheet
Replies
2
Views
226
Skyybot
Skyybot
K
  • Question Question
conditional formatting question
Replies
2
Views
686
kerm007
K
J
  • Solved
Assistance with Index Match formula
Replies
2
Views
332
Jay88
J
Balcomie
  • Solved
Formula For Gematria
Replies
3
Views
200
StephenCrump
S
K
  • Solved
Previously working very complex VBA now stopped
Replies
7
Views
604
Alex Blakenburg
A

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