+ Reply to Thread
Results 1 to 6 of 6

Sort a list object identified by a variable

  1. #1
    Registered User
    Join Date
    07-24-2015
    Location
    Toledo, OH
    MS-Off Ver
    Microsoft Office 2010
    Posts
    85

    Sort a list object identified by a variable

    Okay all, I'm just having trouble figuring out the syntax for this. I know if you have a table called "Table1" whose first column is headed "Column1", you can sort it by doing the following:

    Please Login or Register  to view this content.
    How do you use this syntax if you have only identified the table with a variable? The code I'm using identifies a worksheet based on a selection on the worksheet called "Dashboard", then identifies the table on that worksheet and setting a variable to it. Here's how I wrote that code:

    Please Login or Register  to view this content.
    So now I want to sort the table that is identified by the variable "tbl". But how do I rewrite the code for that? I've tried this:

    Please Login or Register  to view this content.
    ...and this...

    Please Login or Register  to view this content.
    ...and this...

    Please Login or Register  to view this content.
    So now I'm at a loss. How do I do this?

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Sort a list object identified by a variable

    Hello Jerbinator,

    When you Set a variable, you are setting a pointer to an Object. This variable will now behave like the object you have assigned to it.

    Your code then would be...
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-24-2015
    Location
    Toledo, OH
    MS-Off Ver
    Microsoft Office 2010
    Posts
    85

    Re: Sort a list object identified by a variable

    Quote Originally Posted by Leith Ross View Post
    Hello Jerbinator,

    When you Set a variable, you are setting a pointer to an Object. This variable will now behave like the object you have assigned to it.

    Your code then would be...
    Please Login or Register  to view this content.
    The problem with this code is that it's still identifying the table as "Table1", and not as the variable table. The previous code I have written could end up identifying any of over fifty tables. When I tried this code, it gave me runtime error 1004 "Method 'Range' of object '_Global' failed". And I know my variable is identifying the right table because I've got checks in to make sure it does.

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Sort a list object identified by a variable

    Hello Jerbinator,

    Here is a macro that will sort all Tables on "Sheet1" in ascending order using column 1.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-24-2015
    Location
    Toledo, OH
    MS-Off Ver
    Microsoft Office 2010
    Posts
    85

    Re: Sort a list object identified by a variable

    Quote Originally Posted by Leith Ross View Post
    Hello Jerbinator,

    Here is a macro that will sort all Tables on "Sheet1" in ascending order using column 1.
    Please Login or Register  to view this content.
    Leith, I retooled this a little bit and it works great. There's no need for me to sort multiple tables because there's only one table per worksheet, but the variable part of it worked perfectly. Thank you!

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Sort a list object identified by a variable

    Hello Jerbinator,

    You're welcome. Glad I could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] sort a portion of a list of cells based on a variable
    By TallTex6 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-17-2015, 12:57 PM
  2. Replies: 4
    Last Post: 07-12-2013, 12:14 PM
  3. [SOLVED] IF -- ISNumber (Search -- help when there is a variable to be excluded and identified
    By richtree in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-06-2013, 06:07 PM
  4. Replies: 1
    Last Post: 01-30-2013, 08:30 AM
  5. Replies: 6
    Last Post: 12-21-2012, 08:03 AM
  6. Object Name from a Variable, Doing Same Action to Multiple Object
    By 4Ferdinand in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-22-2011, 02:05 AM
  7. Object Variable Not Set Error on Selection object
    By Jean in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2006, 01:50 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1