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

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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

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
433
MHamid
M
gtd526
  • Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
128
gtd526
gtd526
K
  • Solved
Update to existing Move data macro
2
Replies
11
Views
282
Alex Blakenburg
A
R
  • Question
Is There a More Efficient Way to Code This?
Replies
2
Views
351
offthelip
O

Forum statistics

Threads
1,214,929
Messages
6,122,315
Members
449,081
Latest member
tanurai

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