Use Sort method for 2002/2003 in 2000.

[MarMo]

Hi there ,

The options for the Sort method for Ms excel 2000 and MsExcel 2002/2003 are
slightly different.
The differences are the dataoptions in 2002.
I used both sort methods in the same application , but when i run it in
Excel 2000 i get an error .
Error on "xlSortTextasNumbers"
I would like the apllication i'm building to be used on Excel2000 and Excel
2002 / 2003 and sort the data , even if it's another version of Excel.
To verify which Excel the user is using i capture the application.version
value

Is there a way to do this ?
Any suggestion is welcome
Thanks
MarMo

This is the code i'm using :
'declare variable
Public AppVer
-----------------------
Sub SortOutCustomersNbrs()
AppVer = Application.Version
Application.ScreenUpdating = False
Sheets("Clients").Activate
If AppVer < 10 Then
Worksheets("Clients").Range("A1").Sort _
Key1:=Worksheets("Clients").Columns("A"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Else
Worksheets("Clients").Range("A1").Sort _
Key1:=Worksheets("Clients").Columns("A"), _
Header:=xlYes, DataOption1:=xlSortTextAsNumbers
End If
Range("A1").Select
Sheets("Start").Activate
Range("A15").Select
Application.ScreenUpdating = True
End Sub

 

[Bob Phillips]

Just remove it

Worksheets("Clients").Range("A1").Sort _
Key1:=Worksheets("Clients").Columns("A"), _
Header:=xlYes

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[MarMo]

Hello Bob ,

Thanks for your help again. Works fine.
Although i'd like to use the "xlsortTextAsNumbers" in Excel 2002
In one of the sheets ("Clients") column A has ClientNbrs in it (cells are
formatted as text) , for instance :

9522 Client1
..........
15321 Client12
........
95221 Client 253

if I sort it in Excel 2000 the result would be
15321
9522
95221

and in Excel2002 with xlSortTextAsNumbers the result is
9522
15321
95221

So , is there another way to achive this ?

Thanks
MarMo

 

[Bob Phillips]

Okay, try this then.

Take the two sort statements out and put them in their own macros, in their
OWN modules. I called them SortVersion9 and SortVersion10.

Then change your main macro to this

If AppVer < 10 Then
SortVersion9
Else
SortVersion10
End If

Should work.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[MarMo]

Bob ,
Doesn't work , but don't worry.
I'll make 2 different workbooks : 1 for 2000 and 1 for 2002/2003.
Even though i'd like to get it fixed , still all suggestions are welcome.

I made the changes you described. Took all statements and put them in
different modules.
Again in Excel 2000 I get the msg that the variable xlSortTextAsNumbers is
not declared.
I tried to declare it : SrtDef = "xlSortTextAsNumbers" but when running the
code an error msg appears for DataOption1:............
Maybe there is no way around it , because this might be written for
version10 and higher.

My great thanks to you for your effords.
Kind regards
Mario

 

[Bob Phillips]

That is odd, I tested it in XL2000 (not 2002) and it worked fine.

If I get a chance today, I will create a workbook and test it on 2000 and
2002, and then post it.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[MarMo]

Bob ,
Could it be the i need to add or to delete a reference in VBE ?
Mario

 

[Bob Phillips]

No, it's nothing like that.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)