Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem running code | Excel Programming | |||
Problem running VBA 2000 code in Excel 2003 | Excel Programming | |||
XL 2003: Problem running Solver from VBA | Excel Programming | |||
Problem Running Code in Excel 2003 | Excel Programming | |||
Problem running code behind a userform | Excel Programming |