Problem with VBA code not running in Excel 2003
 

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

Problem with VBA code not running in Excel 2003
 

The code is using the sort GUI from the worksheet which may be set to
different settings. try this simplier code. You code wasn't reffering
to the active worksheet when determining the last row which probably
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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183784

Microsoft Office Help

Problem with VBA code not running in Excel 2003
 

On Tue, 2 Mar 2010 03:47:01 -0800, Steve Muir
wrote:

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