Macro to randomly choose numbers
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Macro to randomly choose numbers
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2015
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to randomly choose numbers

    Hi

    I would like to click on a button to have a macro run and choose any amount of random numbers between 1 - 10. So click the button and it may choose 3 random numbers between 1 - 10, click it again and it may choose 5 random numbers, then click and it chooses 9 random numbers and so on.

    I would like to know if there is anyway a macro is capable of this and if so, please advise the code?

    Or am I asking too much?

    If this is not possible then a macro to just choose 1 random number between 1 - 10 please.

    Grant

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,730
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Macro to randomly choose numbers

    So the script chooses 1,6,8,3
    Then what?
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I’m not perfect yet. "Memories are forever"

  3. #3

    Join Date
    Sep 2015
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to randomly choose numbers

    without a macro i guess this works :

    Excel 2010
    ABCDEFGHIJKL
    1
    2from1
    3to10
    4choose5
    5
    6
    78627000000

    Sheet1



    Worksheet Formulas
    CellFormula
    C7=RANDBETWEEN($D$2,$D$3)*(--(COLUMN(C6)-2)<$D$4)
    D7=RANDBETWEEN($D$2,$D$3)*(--(COLUMN(D6)-2)<$D$4)
    E7=RANDBETWEEN($D$2,$D$3)*(--(COLUMN(E6)-2)<$D$4)
    F7=RANDBETWEEN($D$2,$D$3)*(--(COLUMN(F6)-2)<$D$4)
    G7=RANDBETWEEN($D$2,$D$3)*(--(COLUMN(G6)-2)<$D$4)
    H7=RANDBETWEEN($D$2,$D$3)*(--(COLUMN(H6)-2)<$D$4)
    I7=RANDBETWEEN($D$2,$D$3)*(--(COLUMN(I6)-2)<$D$4)
    J7=RANDBETWEEN($D$2,$D$3)*(--(COLUMN(J6)-2)<$D$4)
    K7=RANDBETWEEN($D$2,$D$3)*(--(COLUMN(K6)-2)<$D$4)
    L7=RANDBETWEEN($D$2,$D$3)*(--(COLUMN(L6)-2)<$D$4)



    the -2 thing after the COLUMN function is just to count for columns A and B;

    i formatted the cells where the numbers appears using the following custom format:

    Code:
    [White][=0]General;[Black]General

  4. #4

    Join Date
    Sep 2015
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to randomly choose numbers

    another take with macros on the same sheet structure i presented above:

    Code:
    Sub randomgen()
    
    Range(Range("c7"), Range("c7").End(xlToRight)).Clear
    
    
    Dim F As Integer: F = Range("d2").Value
    Dim T As Integer: T = Range("d3").Value
    Dim R As Integer: R = Range("d4").Value
    
    
    
    
    For Each cell In Range(Cells(7, 3), Cells(7, R + 2))
    cell.Value = Application.WorksheetFunction.RandBetween(F, T)
    Next cell
    
    
    
    
    End Sub
    link the macro with a shape
    Last edited by SpreedsheetCrusader; Dec 17th, 2015 at 08:48 AM.

  5. #5
    New Member
    Join Date
    Dec 2015
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to randomly choose numbers

    Thank you Speedsheetcrusader, that works very well. But I really would like to have it via a macro so that a button has to be clicked.

    Grant

  6. #6
    New Member
    Join Date
    Dec 2015
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to randomly choose numbers

    sorry we crossed over messages, thank you and I will try this and come back soon

  7. #7

    Join Date
    Sep 2015
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to randomly choose numbers

    bro there was some error in my previous code

    i fixed it

    check it again please

    Code:
    Sub randomgen()
    
    Range(Range("c7"), Range("c7").End(xlToRight)).Clear
    
    
    Dim F As Integer: F = Range("d2").Value
    Dim T As Integer: T = Range("d3").Value
    Dim R As Integer: R = Range("d4").Value
    
    
    
    
    For Each cell In Range(Cells(7, 3), Cells(7, R + 2))
    cell.Value = Application.WorksheetFunction.RandBetween(F, T)
    Next cell
    
    
    
    
    End Sub

  8. #8
    New Member
    Join Date
    Dec 2015
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to randomly choose numbers

    Yes that macro works perfectly, thank you so much.

    I do have another question?

    Can I associate a picture to a number that is radomly chosen? This is for display purposes.

  9. #9

    Join Date
    Sep 2015
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to randomly choose numbers

    that is interesting.

    but i am still a noob that is beyond my skills

    i guess you will have to wait for the geeks, scientists, nerds , Excel gods to visit your thread and shows us how this can be done i am sure it is do-able, but that is beyond me.

  10. #10
    New Member
    Join Date
    Dec 2015
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to randomly choose numbers

    Ha ha thank you for what you have advised anyway, you have been very helpful. Maybe I will start a new thread to ask this question.

    Cheers
    G

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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