Using Select Case to pass a variable to AutoFilter as Range

[Kamikazenaz]

Hello All,

First off, thanks for the innumerable solutions, advice, tips and codes that I have used in the past, this is my first post but I've used this forum to solve many an intractable problem. All of my excel / VBA is self-taught, although I have a grounding in programming.

Problem: Running this code I get two error messages at the same line highlighted below.

Msg1 Run time error '91':
Object variable or With block variable not set

Msg 2 Run time error '-2147417848(80010108)':
Automation error
The object invoked has disconnected from its clients

Outcome Desired: The spreadsheet takes an existing datasheet and trims and sorts it according to user input. I have option buttons in the excel front page that allows users to select which field (column) to sort the final output by. I'm trying to use Select Case to pass a variable to the Autofilter function. The top half of the code works (it is borrowed code and the test msg boxes work fine). I just don't know if the Variable 'Cal' is set and passed correctly.

Any help will be much appreciated, thanks in advance.
Cheers!

Public Hal As String

Sub SortSelect()

 Dim OleObj As OLEObject
 Dim Cal As Range
    For Each OleObj In ActiveSheet.OLEObjects
        If OleObj.progID = "Forms.OptionButton.1" Then
            If OleObj.Object = True Then
                Hal = OleObj.Name
                Select Case Hal
                    Case "OBStatus"
                       ' MsgBox Prompt:=Hal & " Selected, Sir!", Buttons:=vbOKOnly, Title:="On Target"
                        Cal = ("$D:$D")
                    Case "OBRegName"
                       ' MsgBox Prompt:=Hal & " Selected, Sir!", Buttons:=vbOKOnly, Title:="On Target"
                        Cal = ("$G:$G")
                    Case "OBRegCode"
                       ' MsgBox Prompt:=Hal & " Selected, Sir!", Buttons:=vbOKOnly, Title:="On Target"
                        Cal = ("$L:$L")
                    Case "OBFormat"
                       ' MsgBox Prompt:=Hal & " Selected, Sir!", Buttons:=vbOKOnly, Title:="On Target"
                        Cal = ("$H:$H")
                    Case "OBMission"
                       ' MsgBox Prompt:=Hal & " Selected, Sir!", Buttons:=vbOKOnly, Title:="On Target"
                        Cal = ("$I:$I")
                    Case "OBNSArea"
                       ' MsgBox Prompt:=Hal & " Selected, Sir!", Buttons:=vbOKOnly, Title:="On Target"
                        Cal = ("$J:$J")
                End Select
            End If
        End If
    Next OleObj
    
 
    Sheets("THECleaner").Select
    Range("$A:$Q").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("THECleaner").AutoFilter.Sort.SortFields.Clear
    [B][COLOR="#800000"]ActiveWorkbook.Worksheets("THECleaner").AutoFilter.Sort.SortFields.Add Key:=Cal, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/COLOR][/B]

    With ActiveWorkbook.Worksheets("THECleaner").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

 

[Gary McMaster]

Hello and welcome aboard!

In the case statements try changing the assignment of your "Cal" variable(s) to look like this:

Set Cal = Range("$J:$J")

 

[Kamikazenaz]

Thanks Gary.

Obvious mistake, I missed the 'Set' statement.
Now I'm getting a MSg:'Compile error: Type mismatch for Set Cal = ("$D:$D")
As a 'Range'-type variable might not be the right one here, should I just use an 'String'-type?

Thanks

 

[Gary McMaster]

You need the word Range between the = and ( as shown in the sample line I posted