Case without select case error compile error

1159 views excel

Hello I keep getting a compile error on this segment of my code and can't find what i'm missing:

Case 5 ' Base oil specification changes?
    ' search datatbl for next empty row
    rw = data.Range.Rows.Count
    If rw = 2 Then
        If data.Range(rw, 1).Value = "" Then
        rw = 1
        data.Range(rw, 1).ListObject.ListRows.Add alwaysinsert:=True
        End If
    End If
    'populate the data table
    With data
    .Range(rw, 1).Offset(1) = Date
    .Range(rw, 2).Offset(1) = "A"
    .Range(rw, 3).Offset(1) = "Marketing"
    .Range(rw, 4).Offset(1) = Me.Controls("lblA" & i).Caption
    End With
    If Me.Controls("OptA" & i & "Y") = True Then
        With data
            .Range(rw, 5).Offset(1) = Me.Controls("OptA" & i & "Y").Caption
            .Range(rw, 6).Offset(1) = Me.Controls("txtA" & i).Value
            .Range(rw, 7).Offset(1) = "PLANT"
        End With
        'add data to summary table
        r = summary.Range.Rows.Count
        If r = 2 Then
            If summary.Range(r, 1).Value = "" Then
            r = 1
             summary.Range(r, 1).ListObject.ListRows.Add alwaysinsert:=True
            End If
        End If
        With summary
            .Range(r, 1).Offset(1) = Me.Controls("lblA" & i).Caption
            .Range(r, 2).Offset(1) = Me.Controls("OptA" & i & "Y").Caption
            .Range(r, 3).Offset(1) = Me.Controls("txtA" & i).Value
            .Range(r, 4).Offset(1) = "PLANT"
        End With
    ElseIf Me.Controls("OptA" & i & "N") = True Then
        data.Range(rw, 5).Offset(1) = Me.Controls("OptA" & i & "N").Caption
    End If

answered question

What line? If its a compile error, it should tell you which line

summary.Range(r, 1).ListObject.ListRows.Add alwaysinsert:=True doesn't look right. Maybe ...ListObjects(1)...

That rather large snippet seems to only include a fraction of the actual full-length procedure. I would warmly recommend extracting each Case block into its own Private Sub procedure, to significantly reduce the line count, reduce the nesting, reduce the cognitive/mental load, and reduce the chances of introducing a compile error by losing track of what block starts/ends where. Also, consistent indentation usually helps preventing such errors.

1 Answer


You need to add Select Case in order to use the Case method followed by the object that is being analyzed. Once complete, end with End Select.

Generic example below showing how to implement the basics of Select Case with the object to analyze is Range("A1")

Select Case Range("A1")
    Case 5
        'Do something if case is met
    Case Else
        'Do something is case is Else
End Select

posted this

Have an answer?


Please login first before posting an answer.