+ Reply to Thread
Results 1 to 25 of 25

Random name generator.

  1. #1
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Random name generator.

    Hi there, I've created macro with a formula. It's a little sloppy but it's the best that I can do, I'm no expert.
    This is to generate a winning name for our weekly draw for our hospital's foundation.
    Please take a look, if someone can make better please do !
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Tabernacle Andre !


    Your VBA procedure revamped :

    PHP Code: 
    Sub Tirage_FHA()
        
    Rows(4).Insert
        
    [A4:B4] = Array(DateSheets(2).Cells(Application.RandBetween(6Sheets(2).[A5].End(xlDown).Row), 1))
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 12-07-2022 at 08:37 PM. Reason: optimization ...

  3. #3
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Re: Random name generator.

    Tabarnacle lol ! Ok je fait quoi avec ça? Ou je l'incère? Je ne suis vraiment pas un expert.

    Un GROS merci

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    89,893

    Re: Random name generator.

    Comme Francophone, je comprends, moi, mais ceci est un forum anglophone - en anglais, svp.
    Last edited by AliGW; 12-08-2022 at 09:51 AM. Reason: Typo corrected.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Re: Random name generator.

    Oh sorry, I responded in french because the person who answered is french. I'll know better next time. No worries, have a great day, and thanks again!

  6. #6
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Re: Tabernacle Andre !

    Hi Mark L, never mind my last reply. I tried it and it works fine but for 1 exception. I've tested it 100 times and I've gotten many doubles (same winner back to back 15 times) and 1 triple (same winner 3 times in a row). I know that I don't have many names but with the other code I hardly gotten any doubles and not 1 triple. It seems to not be that random.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: Random name generator.


    Ok so you need not such random but a random different than the last one or both previous or ?

    (Andre merci de ne pas ecorcher mon prenom STP : Marc avec un c, j'y tiens !)

  8. #8
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Re: Random name generator.

    Lol ok Marc ! This is for a drawing that takes place every month, some quit and some are added but most people stay on. I've worked it a little bit, I'll add it here for you to take a look. What I've got works fine so far.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Random name generator.


    Unclear as with your formula way I also have consecutive duplicates names in the draws :

    Attachment 808573

    So what are the rule(s) : non consecutive same names or what ?

  10. #10
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Re: Random name generator.

    Salut Marc! No rules per say, I'm just saying that I simulated 100 drawings on both codes and yours seems to be less random. It generated more consecutive duplicates and it once generated the same name 3 times in a row.

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: Random name generator.


    How it could be 'in a row' as each name is alone within a row ?

    And, as a reminder, my VBA demonstration uses exactly the same worksheet function than your way !
    Last edited by Marc L; 12-08-2022 at 02:02 PM.

  12. #12
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Re: Random name generator.

    Well Marc the macro copies & pasts the winners in another page and that is where you see the duplicates.

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Try this ...


    According to your post #8 attachment my VBA demonstration revamped to avoid two consecutive duplicates :

    PHP Code: 
    Sub Tirage_FHA_r2d2()
            
    Application.Transpose(Feuil2.Range("A6"Feuil2.[A5].End(xlDown)))
        If [
    C9>""Then
            Feuil3
    .Rows(3).Insert
            Feuil3
    .[A3:C3] = Array([B9], [C9], [C12])
            
    Filter(V, [C9], False)
        
    End If
            [
    B9:C9] = Array(DateV(Application.RandBetween(LBound(V), UBound(V))))
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 12-08-2022 at 02:23 PM. Reason: optimization ...

  14. #14
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Re: Random name generator.

    Ok Marc, thanks a lot ! What if the pool of participants changes positively or negatively, will the code ajust itself ?

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Random name generator.


    Yes as you can see in the Transpose codeline …

  16. #16
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Re: Random name generator.

    Hi Marc, like I said I'm far from being an expert in fact I'm a complete "néophyte" ! Thanks a lot for this I'll try to make it work. Have a great day

  17. #17
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Re: Random name generator.

    Hi Marc, perhaps you can help me once again ! I want the macro to verify a cell to see if there’s a value in it, if not a dialogue box appears telling the user to insert the amount, so far I got it working. But, if there is already an amount in the cell I want it to continue on with the rest of the macro. Can you help with the rest of the code?

    If IsEmpty(Range("C12").Value) = True Then
    MsgBox ("Bien vouloir entrée le montant à gagner")

    What comes afiter this last part ?

  18. #18
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool C12 update ...


    So according to your post #8 attachment :

    PHP Code: 
    Sub Tirage_FHA_bb8()
        If 
    IsEmpty([C12]) Or Not IsNumeric([C12]) Then
            V 
    Application.InputBox(" Montant du gain ?""Tirage"Type:=1):  If False Then Exit Sub
            
    [C12] = V
        End 
    If
            
    Application.Transpose(Feuil2.Range("A6"Feuil2.[A5].End(xlDown)))
            If 
    Not IsEmpty(Feuil3.[B3]) Then V Filter(VFeuil3.[B3], False)
            [
    B9:C9] = Array(DateV(Application.RandBetween(LBound(V), UBound(V))))
            
    Feuil3.Rows(3).Insert
            Feuil3
    .[A3:C3] = Array([B9], [C9], [C12])
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  19. #19
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Re: C12 update ...

    Hi Marc, I appreciate you help very much, but like I said I'm a complete neophyte. I've tried to paste this code and also the last one you gave me and I cannot comprehend hot to make them work. Sorry Perhaps if I give you my file I'm sure that you'd make work easly and much faster than me, what do you think? If not can you suggest someone who can? I need to get this done before the end of the week for the Christmas drawing.

    Sub Tirage_FHA_bb8()
        If IsEmpty([C12]) Or Not IsNumeric([C12]) Then
            V = Application.InputBox(" Montant du gain ?", "Tirage", Type:=1):  If V = False Then Exit Sub
            [C12] = V
        End If
            V = Application.Transpose(Feuil2.Range("A6", Feuil2.[A5].End(xlDown)))
            If Not IsEmpty(Feuil3.[B3]) Then V = Filter(V, Feuil3.[B3], False)
            [B9:C9] = Array(Date, V(Application.RandBetween(LBound(V), UBound(V))))
            Feuil3.Rows(3).Insert
            Feuil3.[A3:C3] = Array([B9], [C9], [C12])
    End Sub


    Sub Tirage_FHA_r2d2()
            V = Application.Transpose(Feuil2.Range("A6", Feuil2.[A5].End(xlDown)))
        If [C9>""] Then
            Feuil3.Rows(3).Insert
            Feuil3.[A3:C3] = Array([B9], [C9], [C12])
            V = Filter(V, [C9], False)
        End If
            [B9:C9] = Array(Date, V(Application.RandBetween(LBound(V), UBound(V))))
    End Sub
    Attached Files Attached Files
    Last edited by Andre St-Jean; 12-12-2022 at 04:20 PM.

  20. #20
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: Random name generator.


    As my previous post #18 replaces all the previous and works with your post #8 attachment
    so I can't guess what could be the issue on your side ?

  21. #21
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Re: Random name generator.

    Hi Marc, again I appreciate your help very much, I just can't get this to work. The issue is I don't what I'm doing lol ! I've replaced everything and I get error messages. I've cleaned up the file for you to put things in the right place, is that ok?
    Attached Files Attached Files
    Last edited by Andre St-Jean; 12-12-2022 at 05:46 PM.

  22. #22
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Random name generator.


    As written the VBA procedure was made upon your post #8 attachment so use the same attachment as it is
    then it should work like on my side when I posted it …

  23. #23
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Re: Random name generator.

    Hi Marc, I understand your impatience and thanks for your help. I'm completely lost here. So I'll keep asking around. No worries have a great week and thanks again.
    Last edited by Andre St-Jean; 12-12-2022 at 06:15 PM.

  24. #24
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Random name generator.


    Tell me first if on your side my post #18 works with your post #8 attachment …

    As your post #21 attachment seems weird, so module2 to be deleted and create a new one.
    Last edited by Marc L; 12-12-2022 at 09:13 PM. Reason: typo ...

  25. #25
    Registered User
    Join Date
    12-07-2022
    Location
    Quebec, Canada
    MS-Off Ver
    Office 365
    Posts
    25

    Smile Re: Random name generator.

    Hi Marc, I got it done. Thanks a whole lot for your help & patience! Have a very merry Christmas & thanks again
    Last edited by Andre St-Jean; 12-14-2022 at 11:00 AM.

+ 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] Random number generator not always generating random numbers
    By Murman01 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-03-2021, 05:29 PM
  2. Replies: 6
    Last Post: 02-20-2019, 12:47 AM
  3. Random Name generator VBA
    By poppeters in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2015, 09:13 PM
  4. Replies: 5
    Last Post: 06-21-2013, 10:40 AM
  5. VBA for random generator
    By arn2025 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-07-2012, 06:45 PM
  6. random name generator
    By rookie37 in forum Excel General
    Replies: 16
    Last Post: 06-23-2009, 03:00 AM
  7. random name generator
    By SRussell in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-25-2007, 06:28 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