Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Reference changes in a macro or VBA

  1. #11
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barrie,

    IT does not like the Range(Cells(1, SortColumn))

    I was thinking along the lines of

    Key1:=Range(Chr(65 + SortColumn) & "2")

    But that will only work if "Divison" is between A & Z

  2. #12
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-08 12:31, JohnJay wrote:
    Barrie,

    IT does not like the Range(Cells(1, SortColumn))

    I was thinking along the lines of

    Key1:=Range(Chr(65 + SortColumn) & "2")

    But that will only work if "Divison" is between A & Z
    John, I really must apologize for not testing this properly (I know, haste makes waste). How about:

    Code:
    Sub SortData()
    Dim SortColumn As Integer, LastColumn As Integer
    Dim LastRow As Long
    LastColumn = Range("A1").End(xlToRight).Column
    LastRow = Range("A65536").End(xlUp).Row
    On Error GoTo ErrorHandler
    SortColumn = Rows("1:1").Find(What:="Division", _
        LookAt:=xlWhole).Column
    On Error GoTo 0
    Range(Cells(1, 1), Cells(LastRow, LastColumn)).Sort _
        Key1:=Range(Cells(1, SortColumn).Address(False, False)), _
        Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    Exit Sub
    ErrorHandler:
        MsgBox prompt:="Could not find a header labelled DIVISION" _
            & Chr(13) & "Data was not sorted", _
            Buttons:=vbCritical + vbOKOnly
    End Sub
    Let's hope I've finally got this pesky thing right

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #13
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barrie,

    your a genius...thank you very much. Many blessing to you. If I can pick your brain for something else, how are you on the rank function?

  4. #14
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-08 12:59, JohnJay wrote:
    Barrie,

    your a genius...thank you very much. Many blessing to you. If I can pick your brain for something else, how are you on the rank function?
    I am familiar with it (although I wouldn't want to hazard a guess as to the extent of my familiarity). What do you want to know?
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #15
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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