+ Reply to Thread
Results 1 to 9 of 9

VBA Code to Sort Words a Second Time

  1. #1
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    VBA Code to Sort Words a Second Time

    The vba code below will sort my row of words based on the number at the end of the words, which is either a '2' or a '1'. Can someone please show me how I can alter the code to sort the words a second time, alphabetically a-z?

    Example:
    word_a 2, word_b 2, word_a 1, word_b 1

    Notice how they're all sorted alphabetically for each number group. Words that have a '2' appended to them are sorted first, followed by words that have a '1' appended to them are sorted next. Anything other than a '1' or '2' appended, like a '?' or a space, would follow after these two groups, and could be in any order.

    I hope that makes sense, and would appreciate any help or suggestions.

    Please Login or Register  to view this content.

  2. #2
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    199

    Re: VBA Code to Sort Words a Second Time

    Could you attach a sample workbook? Would be easier to test.
    Tuomas "Banaanas" Savonius
    Trying to give back now when I actually can do some VBA

  3. #3
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: VBA Code to Sort Words a Second Time

    Quote Originally Posted by banaanas View Post
    Could you attach a sample workbook? Would be easier to test.
    Sure, here you go. The code that sorts the words by row is assigned to the 'Sort Rows' button. So if you add a word or append a higher number to any of the words, and then press the 'Sort Rows' button, it will sort them based on the number appended to each word. At this point everything is fine, but now I would like to add a 2nd sort to this 1st sort, that will sort the actual words per each 'number group' as I described in my original post.

    If you need further examples or have additional questions, just let me know. Thanks again for offering to help!
    Attached Files Attached Files

  4. #4
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    199

    Re: VBA Code to Sort Words a Second Time

    This seems to work the way you intended. I am sorry, I recoded the whole thing to be more flexible. This should work even if you have more than 1 row of data, and every "limit" is checked with code so you don't need to use the editor if the amount of rows or text columns is different every time.
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: VBA Code to Sort Words a Second Time

    Quote Originally Posted by banaanas View Post
    This seems to work the way you intended. I am sorry, I recoded the whole thing to be more flexible. This should work even if you have more than 1 row of data, and every "limit" is checked with code so you don't need to use the editor if the amount of rows or text columns is different every time.
    Wow! Thank you! This looks promising, and an additional thanks for adding detailed comments to your code

    I just tried it though, and every time I press the Sort Rows button I get a "Run-time error '438': object doesn't support this property or method." When I click the debug button, the following line is always highlighted:

    Please Login or Register  to view this content.
    Any suggestions?

    Thanks again!

  6. #6
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    199

    Re: VBA Code to Sort Words a Second Time

    That is wierd. For me it works fine when I download your workbook and paste the code.

    Can you try to run the code, save the workbook and send it here? I will take a look if something is different.

  7. #7
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: VBA Code to Sort Words a Second Time

    Quote Originally Posted by banaanas View Post
    That is wierd. For me it works fine when I download your workbook and paste the code.

    Can you try to run the code, save the workbook and send it here? I will take a look if something is different.
    Sure! Here you go. BTW, all I did was open it, replaced the previous code with yours, saved it. I then pressed the Sort Rows button, and received the error message above. I didn't even add or change any words. I'm running Office 2016 desktop version, not Office 365, if that might make any difference. I know that if you use a lower version than 2016, the Sort.SortFields.Add2 doesn't work, but I'm using 2016, so it shouldn't be an issue.
    Attached Files Attached Files
    Last edited by Modify_inc; 04-19-2019 at 01:27 PM.

  8. #8
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    199

    Re: VBA Code to Sort Words a Second Time

    Ok something wierd is happening, it runs fine for me when I download it. I have no clue why it do not work for you

  9. #9
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: VBA Code to Sort Words a Second Time

    Quote Originally Posted by banaanas View Post
    Ok something wierd is happening, it runs fine for me when I download it. I have no clue why it do not work for you
    If it works on yours, then it has to be something with my Excel or version of Excel. I know that if you use a lower version than 2016, the Sort.SortFields.Add2 doesn't work, but I'm using 2016, so wouldn't think it should be an issue. But it's weird that when I debug it, it always jumps to the ws.Sort.SortFields.Add2 line. Makes me wonder if I need a specific version of Excel 2016. I have v16.0.4266.1001.

+ 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. help for writing a code to sort time and date column
    By mili66 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-14-2015, 08:27 AM
  2. [SOLVED] How to sort words by its value ?
    By MrTanzer in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 07-05-2015, 01:08 PM
  3. Replies: 3
    Last Post: 03-06-2014, 04:44 PM
  4. Sort and total letters in different words
    By rama_chandran in forum Excel General
    Replies: 1
    Last Post: 08-19-2013, 04:34 PM
  5. Sort on number of words (words with least characters first)
    By greenpine in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-23-2012, 10:00 PM
  6. Sort on number of words (words with least characters first)
    By greek in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-23-2010, 04:10 PM
  7. Sort and exclude certain words
    By jds217 in forum Excel General
    Replies: 2
    Last Post: 11-07-2005, 08:40 AM

Tags for this Thread

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