Macro / Sort error

[OniLink]

Hi, in the past I used an earlier version of Excel and this macro worked
fine:

Sub alpha_sort()
'
' alpha_sort Macro
' Macro recorded 1/21/2005 by Admin
'
' Keyboard Shortcut: Ctrl+k
'
Range("D2:CI250").Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
End Sub

But now when I try and run it, it comes up with this error:

Run-Time Error: '1004':
Application-defined or object-defined error

Does anyone know what is going wrong here?

Thanks for any help,
~Oni.

 

[Ken Johnson]

Hi Oni,
I'm using XL 2000 and your macro resulted in that error.
I recorded a macro and noted the last argument
"DataOption1:=xlSortNormal" missing, so I removed this argument from
your code and the error no longer resulted. It seems like an
unnecessary argument so removing it probably has no effect on your
macro's result.
You can also try removing other arguments as well. The macro recorder
often results in a lot of unnecessary code.
Ken Johnson

 

[Roger Govier]

Hi

You don't say what version of Excel you are using, but for me, in XL2003
the following ran fine

Range("D2:CI250").Select
Selection.Sort Key1:=Range("D2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

However, the last line I thought only came in with XL2002 onward, and
does cause problems in XL versions lower than that.
The macro will run under earlier versions and in XL2003 if you amend to

Range("D2:CI250").Select
Selection.Sort Key1:=Range("D2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

 

[OniLink]

Hi,

Thanks for your assitance. I am currently using Excel 2000, but I
believe I originally used Excel 2002 to make this. I tried removing the
line you specified, but it still generates an error. it now outputs:

Run-time error '1004':

Sort method of Range class failed

 

[Roger Govier]

Hi

Try copying the code exactly as posted and placing it between your Sub,
End Sub sections.
I have just tried it again and it works fine for me on both XL2000 and
XL2003.