*Resolved* Excel VBA Sort Functions

[TheRobster]

I am testing an Excel app. that was originally written in Excel 2002 and is also supposed to compatible with Excel 2000. However there is a VBA macro that doesn't seem to work in Excel 2000 (though it works fine in 2002).

The code sorts a table of data in descending order, but when I try to run the macro I get the error message:

Run-time error '1004':

Application-defined or object defined error

This is the offending peice of VBA code:

Selection.Sort Key1:=Range("K21"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

The code is supposed to sort the data table but it just produces an error. The code was recorded in Excel 2002 using the Record Macro function and it would appear there is something in there that Excel 2002 can understand but 2000 can't. Excel 2000 has a Sort function though, so I can't see why this doesn't work.

Can anyone see anything in that code which is only available to Excel 2002 or above?

Cheers
-Rob

[TheRobster]

Right....I've tried recording a similar Macro is Excel 2000. This is the code:

Selection.Sort Key1:=Range("I21"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

There are some differences in the last part of the code.

DataOption1:=xlSortNormal

Is this the problem? Should I edit the code so that it complies with the Excel 2000 syntax? I.e. should I delete this extra bit of code? I don't know if this will bugger up the code though.....

[Br1an_g]

take the section of code out and try.

not sure it would make a difference.

I had a similar issue with a coded search and it turned out to be a corrupt install.

re-installed office and it worked ok.

[TheRobster]

Well I took the final piece of code out and it seems to have solved the problem. Hope it wasn't anything important......the Sort macro works anyway so it obviously didn't need that end bit (finger's crossed).

If someone could confirm what it actually did I would be grateful.

I seem to remember having a couple of problems installing Office 2000.......unreadable files or something like that. Could be something to do with that I suppose.

-Rob

[RobDog888]

The DataOptions are optional. It specifies how to sort text in key 1, 2, 3.

DataOption1 started with 2002 and in 2003 you get DataOption1, DataOption2, DataOption3.

You can sort as xlSortTextAsNumbers or xlSortNormal.

[TheRobster]

RobDogg,

I removed the last line of code so now it just looks like this:

Selection.Sort Key1:=Range("K21"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

It seems to work fine now. Do you think it was okay to delete the DataOption bit? E.g. it seems to work fine now but will this cause problems in the future?

I haven't changed the code in any other way, apart from deleting the DataOption line......

Cheers
-Rob

[RobDog888]

No it should be ok. The only thing that DataOption1 is for specifying the sort order of additional column.
The key column that the sorting is performed on is the first parameter so your ok.

[TheRobster]

Cheers for the help guys.

-Rob