Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    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 _
    :=xlSortNormal
    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,

    Marty

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Range Variable (2007)

    Does this do what you want?
    <code>
    stRange = pCol & "3"
    Selection.Sort Key1:=Range("C3"), Key2:=Range(stRange), _
    Header:=xlGuess, MatchCase:=False
    </code>
    The sort keys have been specified as single cells, and default arguments have been omitted.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •