Results 1 to 10 of 10

Thread: [RESOLVED] Linking cells in Excel?

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    52

    Resolved [RESOLVED] Linking cells in Excel?

    Greetings all,

    I have a set of data which is two columns; the left columns is a series of names and the right column a series of values. The issue I've encountered is that I need to sort the values into size order, but I also need the name to remain next to the value it represents.

    If I haven't explained sufficiently, imagine I have a table of names and heights, I need to sort the heights from lowest to highest, but the names must also be rearranged to remain next to their respective height.

    I have been thinking on this for a while and I can't figure anything out. If anyone has an idea, I'd really appreciate hearing it!

    Thanks!

    P.S. Sorry for bad title. Wasn't sure how to summarise this haha.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Linking cells in Excel?

    you just sort the range of 2 columns, by the second column

    are you trying to do this by code?

    try like
    Code:
    range("a1:b99").sort range("b1")
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    52

    Re: Linking cells in Excel?

    Quote Originally Posted by westconn1 View Post
    are you trying to do this by code?
    I am, although I can't actually figure out the syntax to sort numbers in ascending order. Three different sources have given me different syntax, and all three have led to different errors...

    Any chance you know the correct syntax to sort a range of cells in ascending order?

    I thought it would just be as simple as: myRange.Sort Order1:=xlAscending. But it just returns and object required error.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Linking cells in Excel?

    have you set myrange to a valid range object?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    52

    Re: Linking cells in Excel?

    I simply used:

    Dim myRange as Range
    myRange = Cells("A1:A100")

    Is this incorrect procedure?

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Linking cells in Excel?

    Try like so:

    Code:
    Sub sortRange()
        Dim ws As Worksheet
        Dim myRange As Range
        
        Set ws = ActiveSheet
        Set myRange = ws.Range("a1:b100")
        myRange.Sort key1:=ws.Range("b1"), Order1:=xlAscending
    End Sub

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Linking cells in Excel?

    Dim myRange as Range
    myRange = Cells("A1:A100")
    this should have caused an error, as you need the set keyword for range objects, as in bryces example, also you need to include all columns that have to be reordered
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    52

    Re: Linking cells in Excel?

    Quote Originally Posted by vbfbryce View Post
    Try like so:

    Code:
    Sub sortRange()
        Dim ws As Worksheet
        Dim myRange As Range
        
        Set ws = ActiveSheet
        Set myRange = ws.Range("a1:b100")
        myRange.Sort key1:=ws.Range("b1"), Order1:=xlAscending
    End Sub
    You guys have been a great deal of help.

    Is there any chance I could ask you to explain the syntax of the Sort method? The code you've given me works exactly as intended, but I have no idea why/how it works. I've read the method syntax and descriptions on MSDN, but that hasn't really helped.

    Again, cheers for your help!

  9. #9

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    52

    Re: Linking cells in Excel?

    Btw, if I mark the thread as resolved, does it lock the thread? Or am I OK to mark it as resolved now seeing as my original question has been answered?

  10. #10
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Linking cells in Excel?

    The syntax is similar to when you sort manually in a worksheet. The arguments are first "key" and "sort order," allowing for up to 3 keys (columns to be sorted by). Then you can specify whether or not the columns have headers, and a few other settings that I generally don't change from the default.

Posting Permissions

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



Click Here to Expand Forum to Full Width