Results 1 to 12 of 12

Thread: Define a random sort in Excel

  1. Jun 6th, 2006, 10:07 AM #1
    TheBionicOrange
    TheBionicOrange is offline

    Thread Starter
    Frenzied Member TheBionicOrange's Avatar
    Join Date
    Apr 2001
    Location
    Cardiff, UK
    Posts
    1,818

    Resolved Define a random sort in Excel

    I have 32 names in 32 cells. Against each one is a number.

    I want to create a button (done that bit), and when you click it, it randomly sorts the names against the numbers.

    How do I do that in Excel VBA ?
    Last edited by TheBionicOrange; Jun 6th, 2006 at 10:50 AM.
    Reply With Quote Reply With Quote
  2. Jun 6th, 2006, 10:09 AM #2
    DKenny
    DKenny is offline
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Define a random sort in Excel

    Add an extra (hidden) column that has a =Rand() function beside each name. The when you need to sort just use the random values as your sort key.
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful
    Reply With Quote Reply With Quote
  3. Jun 6th, 2006, 10:33 AM #3
    TheBionicOrange
    TheBionicOrange is offline

    Thread Starter
    Frenzied Member TheBionicOrange's Avatar
    Join Date
    Apr 2001
    Location
    Cardiff, UK
    Posts
    1,818

    Re: Define a random sort in Excel

    Good idea. Done that ... but for some weird reason the 1st entry always stays as the same value, even though its associated randomly generated number changes each time ?!?!?!
    Reply With Quote Reply With Quote
  4. Jun 6th, 2006, 10:34 AM #4
    RobDog888
    RobDog888 is offline
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,709

    Re: Define a random sort in Excel

    Use the Randomize method before any calls the the Rnd function.
    VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    • Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
    Reply With Quote Reply With Quote
  6. Jun 6th, 2006, 10:35 AM #5
    DKenny
    DKenny is offline
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Define a random sort in Excel

    That may be because Excel is treating the first row as a header row. Within your call to the sort method, setting the value of the Header attribute to xlNo should correct this.
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful
    Reply With Quote Reply With Quote
  7. Jun 6th, 2006, 10:37 AM #6
    TheBionicOrange
    TheBionicOrange is offline

    Thread Starter
    Frenzied Member TheBionicOrange's Avatar
    Join Date
    Apr 2001
    Location
    Cardiff, UK
    Posts
    1,818

    Re: Define a random sort in Excel

    Doh !

    That did it.

    Cheers guys

    (Thats the World Cup team sweepstake random generator program taken care of !)
    Reply With Quote Reply With Quote
  8. Jun 6th, 2006, 10:41 AM #7
    DKenny
    DKenny is offline
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Define a random sort in Excel

    Important work indeed!
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful
    Reply With Quote Reply With Quote
  9. Jun 6th, 2006, 10:43 AM #8
    TheBionicOrange
    TheBionicOrange is offline

    Thread Starter
    Frenzied Member TheBionicOrange's Avatar
    Join Date
    Apr 2001
    Location
    Cardiff, UK
    Posts
    1,818

    Re: Define a random sort in Excel

    always
    Reply With Quote Reply With Quote
  11. Jun 6th, 2006, 10:43 AM #9
    RobDog888
    RobDog888 is offline
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,709

    Re: Define a random sort in Excel

    Gambling at work? Tsk tsk.
    VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    • Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
    Reply With Quote Reply With Quote
  12. Jun 6th, 2006, 10:45 AM #10
    TheBionicOrange
    TheBionicOrange is offline

    Thread Starter
    Frenzied Member TheBionicOrange's Avatar
    Join Date
    Apr 2001
    Location
    Cardiff, UK
    Posts
    1,818

    Re: Define a random sort in Excel

    moi ? never :evil grin:
    Reply With Quote Reply With Quote
  13. Jun 6th, 2006, 10:46 AM #11
    RobDog888
    RobDog888 is offline
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,709

    Re: Define a random sort in Excel

    Wahich was it that fixed the issue?
    VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    • Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
    Reply With Quote Reply With Quote
  14. Jun 6th, 2006, 10:48 AM #12
    TheBionicOrange
    TheBionicOrange is offline

    Thread Starter
    Frenzied Member TheBionicOrange's Avatar
    Join Date
    Apr 2001
    Location
    Cardiff, UK
    Posts
    1,818

    Re: Define a random sort in Excel

    Making the header "No" instead of "Guess".

    Should have spotted it !

    Oops ... need to "resolve" the thread ....
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules


Click Here to Expand Forum to Full Width