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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.