If, elseif, else.... Next without for error

[Sphinx404]

Someone please, for the love of God, tell me what my code is missing so I can move on. I keep getting the "NEXT WITHOUT FOR error, and it won't run my macro. I know this has to be a simple fix, but I've been up since 5am... my brain is too fried to look up anymore answers.

Thanks Excel Giants

Sub Step2()


Dim mycell As Range


For Each mycell In Range("E2", Range("E" & Rows.Count).End(xlUp))


If Len(mycell) = 14 Then
    mycell.Offset(, 16).Value = Right(mycell.Value, 8)
ElseIf Len(mycell) = 21 Then
    mycell.Offset(, 16).Value = "UPS"
ElseIf Len(mycell) = 22 Then
    mycell.Offset(, 16).Value = "UPS"
ElseIf (mycell) <= 7 Then
    mycell.Offset(, 16).Value = "Research"
ElseIf Len(mycell) = 9 Then
    mycell.Offset(, 16).Value = "Unknown"
ElseIf Len(mycell) >= 23 Then
    mycell.Offset(, 16).Value = "Research"
Else
    mycell.Offset(, 16).Value = ""


Next mycell


End Sub

 

[Sphinx404]

This code works, but I was trying to simplify it with the code version I posted above.

Sub Step2()


Dim mycell As Range


For Each mycell In Range("E2", Range("E" & Rows.Count).End(xlUp))


If Len(mycell) = 14 Then
    mycell.Offset(, 16).Value = Right(mycell.Value, 8)
End If


If Len(mycell) = 21 Then
    mycell.Offset(, 16).Value = "UPS"
End If


If Len(mycell) = 22 Then
    mycell.Offset(, 16).Value = "UPS"
End If


If Len(mycell) <= 7 Then
    mycell.Offset(, 16).Value = "Research"
End If


If Len(mycell) = 9 Then
    mycell.Offset(, 16).Value = "Unknown"
End If


If Len(mycell) >= 23 Then
    mycell.Offset(, 16).Value = "Research"
End If

Next mycell

End Sub

 

[Norie]

Perhaps a Select Case?

Dim mycell As Range


    For Each mycell In Range("E2", Range("E" & Rows.Count).End(xlUp))

        Select Case Len(mycell)
            Case 14
                mycell.Offset(, 16).Value = Right(mycell.Value, 8)
            Case 21, 22
                mycell.Offset(, 16).Value = "UPS"
            Case Is <= 7
                mycell.Offset(, 16).Value = "Research"
            Case 9
                mycell.Offset(, 16).Value = "Unknown"
            Case Is >= 23
                mycell.Offset(, 16).Value = "Research"
        End Select

    Next mycell

 

[My Aswer Is This]

Your original post has a else statement but your second script did no have a else statement.

If you need no else statement use this:

Sub Step2()
Dim mycell As Range
For Each mycell In Range("E2", Range("E" & Rows.Count).End(xlUp))
If Len(mycell) = 14 Then mycell.Offset(, 16).Value = Right(mycell.Value, 8)
If Len(mycell) = 21 Then mycell.Offset(, 16).Value = "UPS"
If Len(mycell) = 22 Then mycell.Offset(, 16).Value = "UPS"
If (mycell) <= 7 Then mycell.Offset(, 16).Value = "Research"
If Len(mycell) = 9 Then mycell.Offset(, 16).Value = "Unknown"
If Len(mycell) >= 23 Then mycell.Offset(, 16).Value = "Research"
Next mycell
End Sub

 

[MARK858]

... and just so you understand the reason your code in post number 1 errors is that you are missing an End If above the Next mycell line.