Using Select Case to pass a variable to AutoFilter as Range

Kamikazenaz

New Member
Joined
Jan 21, 2015
Messages
3
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!



Code:
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
 
Last edited:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
Hello and welcome aboard!

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

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

Kamikazenaz

New Member
Joined
Jan 21, 2015
Messages
3
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
 

Forum statistics

Threads
1,136,322
Messages
5,675,075
Members
419,549
Latest member
EliteBeat

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
Top