Results 1 to 11 of 11
  1. #1
    Ann
    Guest

    Sorting lists randomly

    I have been told that you can randomly sort lists in Word and Excel. I am Secretary of our local performing arts society and have a list of competitors who enter several classes. When the programme is printed I would like to be able to randomly sort them in each different class so that the competitors are in a different order in each class. Is there a way to number them at the same time? I am using Word 97.

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Sorting lists randomly

    Ann,

    This surely sounds like it would be a useful function, but a quick look through Excel and Word (2000) help indicates nothing of the 'sort'.

    Here's a homemade random sorter via a macro. It assumes that you have first selected the paragraphs which you wish to have sorted randomly.

    If you're unsure how to use this code, post back.

    Gary

    <pre>Sub RandomSort()
    'Gary Frieder April 2001
    'Posted on Woody's Lounge Word forum
    'Purpose: Randomly sort a range of paragraphs
    'Requirements: The range of paragraphs must be selected first
    Dim lngParaCt As Long
    Dim rngCurRng As Range
    Set rngCurRng = Selection.Range
    Dim n As Long
    Dim i As Long
    Dim intRandVal As Integer
    lngParaCt = rngCurRng.Paragraphs.Count
    For n = 1 To lngParaCt
    intRandVal = Int((lngParaCt * Rnd) + 1)
    rngCurRng.Paragraphs(n).Range.InsertBefore (CStr(intRandVal) & vbTab)
    Next n

    rngCurRng.Sort ExcludeHeader:=False, FieldNumber:="Paragraphs", _
    SortFieldType:=wdSortFieldNumeric, SortOrder:=wdSortOrderAscending, Separator:= _
    wdSortSeparateByTabs, SortColumn:=False, CaseSensitive:=False

    With Selection
    For i = 1 To lngParaCt
    rngCurRng.Paragraphs(i).Range.Select
    .Collapse
    .Extend Character:=vbTab
    .Delete Unit:=wdCharacter, Count:=1
    Next i
    End With

    End Sub
    </pre>

    Notes to the VBA-enabled: this can probably be made a bit more elegant (particularly w/r/t the use of "Selection"), but hey it's late and I just wanted to knock out something that works <g>. Also, the use of "Rnd" is straight from Word VBA help - I didn't take the time to suss out precisely how that line of code works...

  3. #3
    Ann
    Guest

    Re: Sorting lists randomly

    Thank you Gary for your quick reply. I tried to use this by recording it as a macro but when I told it to Run the macro printed. Sorry to be so dumb - I have used macros before but never with VBA, so would really appreciate further instructions on the use of your sorter. Thanks a lot.

  4. #4
    crudd
    Guest

    Re: Sorting lists randomly

    Copy the macro text into your clipboard
    Load Word
    Press Alt+F11 to load the VBA editor
    You should be in a module called NewMacros according to the Project Explorer pane on the left. If this pane isnt present, use Ctrl+R to reveal it.
    If you arent in Normal / Modules / Newmacros you should try to browse to there using the Project Explorer. If there are no modules under the Normal template, or there is no Newmacros module, then use your task bar to go back to word, record a dummy macro (just type a few keys and stop recording), and then flip back to the VB editor.

    At this point you have double-clicked on the Normal / Modules / Newmacros module and it has opened up on the right pane. Paste your code in here, leave the cursor anywhere inside and press run (it looks like the play key, or use F5.)

    Now, to make this better, you can paste the code into your own template, go back to word, rightclick a toolbar and select customise.
    Select commands tab and then scroll down the left list until you find macros, highlight this. Look for your macro in this list, and when you find it, drag it to somewhere on your toolbars. By pressing the button created you will launch this macro.

    Or you can assign a shortcut key to this macro, via the same customise button, but by selecting the Keyboard button down the bottom of the dialog.

  5. #5
    Ann
    Guest

    Re: Sorting lists randomly

    Thank you Gary and Crudd for your replies to my query. I have completed the loading of the macro and assigned a shortcut key (AltS) but when I try to use it in my Word document I get the message - Microsoft Visual Basic - Compile error: Invalid outside procedure. Help!

  6. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Sorting lists randomly

    Hi Ann,

    This should be fairly easy to track down - it sounds like you've got some extraneous code, either before the "Sub Random Sort()" statement, or else after the "End Sub" statement.

    To track down the offending code, go into the VB Editor (Alt+F11 from Word) and locate the module containing your code so that the code is visible onscreen.
    Click an insertion point anywhere in the code.
    Then look up on the top toolbar for a menu item called "Debug" - click on this and then click on "Compile Project".

    You will probably get an error message at that point, and the problem line of code will probably appear with blue highlighting at the start of the line.

    If you recognize it as something belonging to the macro I posted, then it will need to go back into the correct spot, or alternatively try pasting the whole macro in again.

    If it's something extraneous than you can probably just delete it. The key is that you want to be able to click on Compile Project, and not get an error.

    If you still run into a problem, just post back. It is a fun macro, so I'm looking forward to your getting it running! :-)

    Gary

  7. #7
    Ann
    Guest

    Re: Sorting lists randomly

    Thanks for the fast reply. I used the Debug and it hightlighted the 3rd line - Set rngCurRng = Selection.Range.
    Tried deleting Set then it highlighted Selection so I copied and pasted the whole macro again. Still received the same error message. After the "End Sub" statement and before the "Sub Random Sort()" statement there is only a line. I really appreciate your assistance with this.

  8. #8
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Sorting lists randomly

    Hmmm.. this is one of those things that's harder to sort out without laying eyes on it. What is the line that's before and after End Sub and Sub? - there really shouldn't be anything else in the module.

    There shouldn't be anything wrong with the Set rngCurRng = Selection.Range, as long as you still have the Dim rngCurRng As Range statement there above it.

    Here's the code again, this time without the formatting tags - to try this again, make sure that your code module has absolutely nothing in it, then select the following code, copy it, and paste it into your empty code module. It oughta work, but post back if it doesn't.

    (And to state the obvious, you must have a Word document open before you try to run this code, and have the paragraphs you want to have sorted, selected).

    To run it from the Word document, go to Tools>Macro>Macros, look for and select the macro name in the list, and then press Run.

    Sub RandomSort()
    'Gary Frieder April 2001
    'Posted on Woody's Lounge Word forum
    'Purpose: Randomly sort a range of paragraphs
    'Requirements: The range of paragraphs must be selected first
    Dim lngParaCt As Long
    Dim rngCurRng As Range
    Set rngCurRng = Selection.Range
    Dim n As Long
    Dim i As Long
    Dim intRandVal As Integer
    lngParaCt = rngCurRng.Paragraphs.Count
    For n = 1 To lngParaCt
    intRandVal = Int((lngParaCt * Rnd) + 1)
    rngCurRng.Paragraphs(n).Range.InsertBefore (CStr(intRandVal) & vbTab)
    Next n

    rngCurRng.Sort ExcludeHeader:=False, FieldNumber:="Paragraphs", _
    SortFieldType:=wdSortFieldNumeric, SortOrder:=wdSortOrderAscending, Separator:= _
    wdSortSeparateByTabs, SortColumn:=False, CaseSensitive:=False

    With Selection
    For i = 1 To lngParaCt
    rngCurRng.Paragraphs(i).Range.Select
    .Collapse
    .Extend Character:=vbTab
    .Delete Unit:=wdCharacter, Count:=1
    Next i
    End With

    End Sub

  9. #9
    Ann
    Guest

    Re: Sorting lists randomly

    Hi Gary
    The line that's after End Sub at the end of your macro and before Sub randomsort()is an underscore line that appeared after I pasted the macro, pressed run or F5 and named the macro randomsort.

    I made sure that the code module was completely empty, selected the code from your latest email (from "Dim lngParaCt As Long ..... Next i End With End Sub"), copied it and pasted it into the empty code module.

    Then I opened a Word document, typed in some people's names on six different lines, ie Jane Green Jill Black Jenny Scott Mary Smith Kate White Glenda Brown - selected these names - went to Tools, Macro, selected the macro name in the list and pressed Run. When I did it last time I assigned a keyboard shortcut to the macro but thought that this time I would follow your instructions to the letter. The same message came up - Compile error: Invalid outside procedure. I went through the Debug option and still got the highlighting on the word Set.

    I assume that this macro works for you so can't understand where I'm going wrong. Your time and patience is very much appreciated.
    Ann

  10. #10
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Sorting lists randomly

    Hi again Ann,

    It still sounds like the code is not getting set up right in the module, somehow: the very first line should be the one that says: "Sub Random Sort()".

    Anyway, attached to this message is a sample Word file which contains a working version of the macro. Just click on the attachment - you will probably get a prompt about accepting macros - say Yes or Enable or whatever - and then follow the instructions to test run the macro.

    If you want to save the attached file to your own hard drive, just use File>SaveAs and save it to your PC.

    Hope it works this time.. {[img]/forums/images/smilies/ohmy.gif[/img])

    Gary
    Attached Files Attached Files

  11. #11
    Ann
    Guest

    Re: Sorting lists randomly

    Hi Gary

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> As soon as I read your reply telling me that the very first line should be the one that says "Sub Random Sort()" I realised where I had gone wrong. I only copied the macro starting at "Dim lngParaCt As Long" instead of starting at Sub RandomSort(). Sorry to have been so dumb but I've never used VBA before. I cleared out the incorrect macro and copied and pasted your macro again (all of it this time) and it worked! I also tried the document you sent with your last email and it worked for me too.

    Once again thank you Gary for your time and patience - the macro will be very useful when doing our competition programmes. Hope someone else who reads Woodys Lounge will be able to use it too.

    Ann

Posting Permissions

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