We have made many tests with Excel 2007 4 products and 5 years and cannot replicate the error. If you could reply to the notification email of this post with your template, then we can run it with your data and resolve this.
I am running these two macros in my spreadsheet in Excel 2007 and they run perfectly. However, my colleague sees a runtime 438 error when she tries to run it in Excel 2003. Any ideas? PLEASE help.
See my macros below:
Macro 1)
VBA Code:
Sub Sort() ' ' Sort Macro
ActiveWorkbook.Worksheets("Value of Incentives by Cust").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Value of Incentives by Cust").Sort.SortFields.Add _ Key:=Range("U19:U80"), SortOn:=xlSortOnValues, Order:=xlDescending, _ DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Value of Incentives by Cust").Sort .SetRange Range("O19:U80") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub
The Excel run time error 438 occurs when an method is not supported for the object in the VBA code. If a macro has been recorded in a later version of Excel, the object method may not be compatible with an earlier version.
In order to locate the exact problem, click on the Debug button when the error message appears and see which line of code is highlighted in the VBA project.
The sorting functionality is more advanced in Excel 2007 and 2010 than Excel 2003 and prior. For Excel 97-2003 simple sorting cannot be cleared as the data order is simply changed and the method is not stored in memory.
A filter can however be both set and cleared. To set a filter, sort on a column and then clear the filter, the following code (with comments) can be used.
VBA Code:
'Select the region Sheets("Value of Incentives by Cust").Select Range("O19:U80").Select
'Create a filter on the selected region Selection.AutoFilter
'Sort a column - add more with Key2 etc Range("U19:U80").Sort Key1:=Range("U19"), Order1:=xlDescending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal
'Clear the filter (note that this is the same as setting a filter so if one exists it will clear it and if not it will add one). Selection.AutoFilter
When I post the following code in VBA "If Target.Worksheets("Sheet2").Column = 2 And Target.Worksheets("Sheet2").Row = 3 Then" I get a run time error 438 prompt. What do I need to do to avoid this error prompt when trying to specify which worksheet a row and column are located in?
