bdsii
02-25-2010, 11:56 AM
I run Excel 2007 and have code that works perfectly with that version but just found out it will not work with Excel 2003. See sample spreadsheet attached.
I was aware to be cautious about the conflict and tried to eliminate as much as I could by using a 2003 version spreadsheet (.xls file) and I have tried to make sure the number of rows called out in the code do not go above the max for 2003. I thought the code I was using was compatible with both versions.
Upon trying to run the macro, I got a Run Time Error '438' - object doesn't support this property or method.
I commented out the code below and the macro ran fine. Now I need to convert the following sort to one that will work for Excel 2003.
I was not able to debug at the location that was using 2003 but I did quickly see the row highlighted for debugging was the second row of the code with clear at the end of the line. I cannot work from the site using 2003 and do not have access to another location that has 2003 to tinker with the code.
Can someone familiar with 2003 eyeball this and let me know what may be causing the error? I have tried searching but cannot find anything on this.
A second question: with VBA, does 2003 allow more than 3 sort columns (used below) or is it restricted to 3 columns?
Any advice ?
Sub sort()
Dim totalrows As Long
totalrows = 0
totalrows = ActiveSheet.UsedRange.Rows.Count
Rows("2:" & totalrows).Select
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Clear
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"C3:C" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"A3:A" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"I3:I" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"H3:H" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"D3:D" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("HoursAvail").sort
.SetRange Range("A2:I" & totalrows)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
Thanks in advance for the help !
I was aware to be cautious about the conflict and tried to eliminate as much as I could by using a 2003 version spreadsheet (.xls file) and I have tried to make sure the number of rows called out in the code do not go above the max for 2003. I thought the code I was using was compatible with both versions.
Upon trying to run the macro, I got a Run Time Error '438' - object doesn't support this property or method.
I commented out the code below and the macro ran fine. Now I need to convert the following sort to one that will work for Excel 2003.
I was not able to debug at the location that was using 2003 but I did quickly see the row highlighted for debugging was the second row of the code with clear at the end of the line. I cannot work from the site using 2003 and do not have access to another location that has 2003 to tinker with the code.
Can someone familiar with 2003 eyeball this and let me know what may be causing the error? I have tried searching but cannot find anything on this.
A second question: with VBA, does 2003 allow more than 3 sort columns (used below) or is it restricted to 3 columns?
Any advice ?
Sub sort()
Dim totalrows As Long
totalrows = 0
totalrows = ActiveSheet.UsedRange.Rows.Count
Rows("2:" & totalrows).Select
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Clear
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"C3:C" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"A3:A" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"I3:I" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"H3:H" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"D3:D" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("HoursAvail").sort
.SetRange Range("A2:I" & totalrows)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
Thanks in advance for the help !