Problem with VBA code not running in Excel 2003

[Steve Muir]

Hi All,

I have recently developed a sheet which checks the distance between 2
postcodes and then outputs results to another sheet and sorts them by
distance (closest to furthest).

This works perfectly in Excel 2007 (which I developed the sheet with) but
when my collegue runs it in 2003 the code for the sort fails. The code is:

Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Agencies by Distance").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Agencies by Distance").Sort.SortFields.Add
Key:=Range("F7"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Agencies by Distance").Sort
.SetRange Range("A7:H1000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

It fails on the second line of code and I don't know why? Can anyone shed
any light on this for me.

many thanks

Stephen Muir

 

[joel]

The code is using the sort GUI from the worksheet which may be set t
different settings. try this simplier code. You code wasn't refferin
to the active worksheet when determining the last row which probabl
gave the error.


with ActiveWorkbook.Worksheets("Agencies by Distance")
Set LastRow = .Range("A7").End(xldown).Row
Set sortRange = .Range("A7:H" & Lastrow)

SortRange.Sort _
Header:=Xlno
key1:=.Range("F7"),
Order1:=xlascending
end with

 

[Ron Rosenfeld]

Hi All,

I have recently developed a sheet which checks the distance between 2
postcodes and then outputs results to another sheet and sorts them by
distance (closest to furthest).

This works perfectly in Excel 2007 (which I developed the sheet with) but
when my collegue runs it in 2003 the code for the sort fails. The code is:

Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Agencies by Distance").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Agencies by Distance").Sort.SortFields.Add
Key:=Range("F7"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Agencies by Distance").Sort
.SetRange Range("A7:H1000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

It fails on the second line of code and I don't know why? Can anyone shed
any light on this for me.

many thanks

Stephen Muir

Well, for one thing, I don't believe the SortMethod member was present prior to
XL2007. Since it's used for Chinese languages according to HELP, you might be
able to omit it. If not, perhaps someone has a workaround to use in XL2003.
--ron