Results 1 to 2 of 2
Thread: VBA Range Variable (2007)
2008-06-27, 01:34 #1
- Join Date
- Jul 2002
- Sacramento, CA
- Thanked 1 Time in 1 Post
VBA Range Variable (2007)
I've discovered the sort syntax created using Excel 2007 in macro record mode does not work when run using Excel 2003, even though the 2007 Workbook was saved in the Excel 97-2003 format. I thought the solution would be simple, just record a new sort macro while running Excel 2003. For the most part that provided a quick solution, however in one of my macros I pass the column value as parameter and create a sort range variable: e.g
stRange = pCol & "3:" & pCol & "54". But I find the 2003 sort syntax:
Selection.Sort Key1:=Range("C3:C54"), Order1:=xlAscending, Key2:=Range(stRange) _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
generates a Run-Time error 1004 ...The sort reference is not valid when the macro is executed. I don't have Excel 2003 on my computer to be able to play with syntax posibilities.
Appreciate any thought and isights,
2008-06-27, 01:49 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: VBA Range Variable (2007)
Does this do what you want?
stRange = pCol & "3"
Selection.Sort Key1:=Range("C3"), Key2:=Range(stRange), _
The sort keys have been specified as single cells, and default arguments have been omitted.