CODEWITHSUNDEEP
vbaexcel
Matt Taylor
Matt Taylor

Reputation: 671

Compile error 'Case without Select Case' when adding a check statement

This is for renaming files. Most files have a certain character length so I can modify them easily. One length though is similar. I am trying to add another check to see if the cell is all numbers after the MID(LEN function.

Getting a compile-time error when I add the IsNumeric(-(Mid(aCell, 2, Len(aCell) - 5))) check:

Case without Select Case

Where am I going wrong?

Below works fine:

Case 13
    Check = Left(aCell, 1)
        If Check = "e" Then 'Existing Standard
        val = "S-" & Left(aCell, Len(aCell) - 13) & Mid(aCell, 2, Len(aCell) - 10) & "-" & Mid(aCell, 5, Len(aCell) - 9)
            Else 'Standard after page 9
            val = "S-" & Left(aCell, Len(aCell) - 10) & "-" & Mid(aCell, 4, Len(aCell) - 9)
        End If
    Check = ""

I am trying to add in this code to the above Case 13 code.

    Check = IsNumeric(-(Mid(aCell, 2, Len(aCell) - 5)))
        If Check = True Then 'Existing Three Line Diagrams
        val = "S-" & Mid(aCell, 3, Len(aCell) - 10) & "-" & Mid(aCell, 6, Len(aCell) - 9)

Full code:

Option Explicit

Sub Convert()
Application.ScreenUpdating = False

Dim rng As Range, aCell As Range
Dim val As String, Check
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A2:A" & LastRow)

For Each aCell In rng.Cells
Select Case Len(aCell)
    Case 12
        Check = Left(aCell, 1)
            If Check = "0" Or Check = "c" Or Check = "e" Then 'Three Line Diagram
            val = "S-" & Mid(aCell, 2, Len(aCell) - 9) & "-" & Mid(aCell, 5, Len(aCell) - 8)
                Else 'Standard
                val = "S-" & Left(aCell, Len(aCell) - 9) & "-" & Mid(aCell, 4, Len(aCell) - 8)
            End If
        Check = ""
    Case 13 '|||Problem Area|||
        'Check = IsNumeric(-(Mid(aCell, 2, Len(aCell) - 5)))
            'If Check = True Then 'Existing Three Line Diagrams
            'val = "S-" & Mid(aCell, 3, Len(aCell) - 10) & "-" & Mid(aCell, 6, Len(aCell) - 9)
                'Else
                Check = Left(aCell, 1)
                If Check = "e" Then 'Existing Standard
                val = "S-" & Left(aCell, Len(aCell) - 13) & Mid(aCell, 2, Len(aCell) - 10) & "-" & Mid(aCell, 5, Len(aCell) - 9)
                    Else 'Standard after page 9
                    val = "S-" & Left(aCell, Len(aCell) - 10) & "-" & Mid(aCell, 4, Len(aCell) - 9)
            End If
        Check = ""
    Case 14 'Existing Standard after page 9
        val = "S-" & Left(aCell, Len(aCell) - 14) & Mid(aCell, 2, Len(aCell) - 11) & "-" & Mid(aCell, 5, Len(aCell) - 10)
    Case 15 'SD Standard
        val = "SD-" & Left(aCell, Len(aCell) - 15) & Mid(aCell, 5, Len(aCell) - 12) & "-" & Mid(aCell, 8, Len(aCell) - 12)
    Case 16 'Reference or Removal
        val = Left(aCell, Len(aCell) - 9) & "-" & (Mid(aCell, 8, Len(aCell) - 12))
    Case 17 'Reference or Removal after page 9
        val = Left(aCell, Len(aCell) - 10) & "-" & (Mid(aCell, 8, Len(aCell) - 13))
 On Error GoTo whoa
    Case Else 'All other pages
        val = "_Mod " & Left(aCell, Len(aCell) - 4)
End Select

val = UCase(val)

val = val & " " & aCell.Offset(, 2) & aCell.Offset(, 3)

aCell.Offset(, 1).Value = val
Next
Call RemoveZero
Call RemoveBadChar
    Range("C1").Select
    Worksheets("Rename").Columns("B").AutoFit
    Application.ScreenUpdating = True
whoa:
Application.ScreenUpdating = True
Exit Sub
End Sub

Upvotes: 1

Views: 3770

Answers (1)

BruceWayne
BruceWayne

Reputation: 23285

With that code, you need an additional End If.

Case 13
        Check = IsNumeric(-(Mid(aCell, 2, Len(aCell) - 5)))
        If Check = True Then 'Existing Three Line Diagrams
            val = "S-" & Mid(aCell, 3, Len(aCell) - 10) & "-" & Mid(aCell, 6, Len(aCell) - 9)
        Else
            Check = Left(aCell, 1)
        End If '''' THIS WAS MISSING

        If Check = "e" Then  'Existing Standard
            val = "S-" & Left(aCell, Len(aCell) - 13) & Mid(aCell, 2, Len(aCell) - 10) & "-" & Mid(aCell, 5, Len(aCell) - 9)
        Else                 'Standard after page 9
            val = "S-" & Left(aCell, Len(aCell) - 10) & "-" & Mid(aCell, 4, Len(aCell) - 9)
        End If

        Check = ""
Case 14
....

Currently, you have If / Else / If / Else which you can't do without an End If before that second If. (Or of course, change that block to If / ElseIf / ElseIf / ... / End If or something similar).

Upvotes: 1

Related Questions