Excel VBA Error Code 438 Object doesn support this property of method Issue!!

Evening

Can someone help me and shed some light for me

I have a spreadsheet quite simple contains list of daa and has VBA and autofilter in order to filter criteria without need to go into filter properties yourself.  Problem is I am running Excel 2007, the original spreadsheet and code was created in 2003 and all amendments since have been done by me in 2007.  I copied the VB into exisitng worksheet today and was found to be full of errors.  Code 438 Object does not support property or method.  The code and spreadsheet when used in 2007 version works fine but not in 2003.  Any ideas

I have attached test file and also code.  
Private Sub OptionButton1_Click()
'
' ConsReport Asc Macro
'
    Dim myLastCell As Range
    Dim mySortRange As Range
    
    Set myLastCell = LastCell(ActiveSheet)
    Set mySortRange = Range("J4", Cells(myLastCell.Row - 2, 10))
    
        
    
    Columns("G").Hidden = False
    Columns("J").Hidden = False
    Columns("I").Hidden = True
    
    Range("J4").Select
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Add Key:=mySortRange, SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
    End With
    
End Sub

Private Sub OptionButton2_Click()
'
' ft2Report Asc Macro
'

    Dim myLastCell As Range
    Dim mySortRange As Range
    
    Set myLastCell = LastCell(ActiveSheet)
    Set mySortRange = Range("I4", Cells(myLastCell.Row - 2, 9))
    
    Columns("G").Hidden = True
    Columns("J").Hidden = True
    Columns("I").Hidden = False
    Range("I4").Select
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Add Key:=mySortRange, SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
    End With
    
End Sub

Private Sub OptionButton3_Click()
'
' ConsReport Desc Macro

   Dim myLastCell As Range
   Dim mySortRange As Range
    
   Set myLastCell = LastCell(ActiveSheet)
   Set mySortRange = Range("J4", Cells(myLastCell.Row - 2, 10))
   
    Columns("G").Hidden = False
    Columns("J").Hidden = False
    Columns("I").Hidden = True
    Range("J4").Select
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Add Key:=mySortRange, SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
    End With
    
End Sub

Private Sub OptionButton4_Click()
'
' ft2Report Desc Macro

   Dim myLastCell As Range
   Dim mySortRange As Range
    
   Set myLastCell = LastCell(ActiveSheet)
   Set mySortRange = Range("I4", Cells(myLastCell.Row - 2, 9))

    Columns("G").Hidden = True
    Columns("J").Hidden = True
    Columns("I").Hidden = False
    Range("I4").Select
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Add Key:=mySortRange, SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
    End With
    
End Sub
Function LastCell(ws As Worksheet) As Range
'
' Note "&" denotes a long value; "%" denotes an integer value
  
  Dim LastRow&, lastCol%

' Error-handling is here in case there is not any
' data in the worksheet

  On Error Resume Next

  With ws

  ' Find the last real row

    LastRow& = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

  ' Find the last real column

    lastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column

  End With

' Finally, initialize a Range object variable for
' the last populated row.

  Set LastCell = ws.Cells(LastRow&, lastCol%)

End Function

Sub ClearCriteria()

If ActiveSheet.AutoFilterMode = True Then

    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields.Clear
    
    Columns("G").Hidden = False
    Columns("J").Hidden = False
    Columns("I").Hidden = False
    
    Else
    


End If

End Sub

Open in new window

Test-Report.xls
stefone1981Asked:
Who is Participating?
 
Rory ArchibaldCommented:
Your option button routines should be as below.
As I mentioned, you really ought to be developing in the earliest version you need to support - it will make your life much easier.

Private Sub OptionButton1_Click()
'
' ConsReport Desc Macro
'
    
    Columns("G").Hidden = False
    Columns("J").Hidden = False
    Columns("I").Hidden = True
    
    Me.AutoFilter.Range.Sort Key1:=Range("J3"), Order1:=xlDescending, _
                                                    dataOption1:=xlSortNormal, header:=xlYes, MatchCase:=False
        
    
End Sub

Private Sub OptionButton2_Click()
'
' ft2Report Desc Macro
'
    Columns("G").Hidden = True
    Columns("J").Hidden = True
    Columns("I").Hidden = False
    Me.AutoFilter.Range.Sort Key1:=Range("I3"), Order1:=xlDescending, _
                                                    dataOption1:=xlSortNormal, header:=xlYes, MatchCase:=False
    
End Sub

Private Sub OptionButton3_Click()
'
' ConsReport Asc Macro

    Columns("G").Hidden = False
    Columns("J").Hidden = False
    Columns("I").Hidden = True
    
    Me.AutoFilter.Range.Sort Key1:=Range("J3"), Order1:=xlAscending, _
                                                    dataOption1:=xlSortNormal, header:=xlYes, MatchCase:=False
    
End Sub

Private Sub OptionButton4_Click()
'
' ft2Report Asc Macro

    Columns("G").Hidden = True
    Columns("J").Hidden = True
    Columns("I").Hidden = False
    Me.AutoFilter.Range.Sort Key1:=Range("I3"), Order1:=xlAscending, _
                                                    dataOption1:=xlSortNormal, header:=xlYes, MatchCase:=False
    
End Sub

Open in new window

0
 
Rory ArchibaldCommented:
Autofilter does not support Sort in earlier versions. This is why you should always develop in the earliest version you have to support.
0
 
stefone1981Author Commented:
Hi

What recommendations have you for this scenario. could you shed some lights on potential other methods

All I am after achieving is putting permanent Autofilter in place and I have 4 option buttons, each option button focuses on one particular column and sorts either asc or desc.

this isnt the only issue we are having when it comes to working across multiple versions.

Can this be achieved to work in 2003 vesion.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.