Generate Random Numbers (Integers) in Microsoft Excel

it started with a question: http://www.mrexcel.com/forum/showthread.php?t=488078
in short the question was: numbers 1 to 90 ->random list with 30 of them.
then i was really tempted to make a generic solution and i think i did.
Below is a UDF which will generate random integer numbers within chosen limits. If you also decide to fill the Range parameter of the function it will try to generate numbers which are not present in this range. if range is omitted it becomes something like RANDBETWEEN.
When further unique numbers cannot be found (all have been used already) it will return #NOMORE.
so when you use a range like A$1:A2 then filling down will expand the range and you will get a list of unique random numbers.
i did choose not to make it Volatile, but anyone who needs this will know why and how to add it.

Code:

Public Function RandUI42(LowerLimit As Long, UpperLimit As Long, Optional CompareRange As Range) As Variant
    Dim ll As Long, ul As Long
    ll = Application.WorksheetFunction.Min(LowerLimit, UpperLimit)
    ul = Application.WorksheetFunction.Max(LowerLimit, UpperLimit)
    If CompareRange Is Nothing Then
        RandUI42 = Round(ll + (ul - ll) * Rnd(), 0)
        Exit Function
    End If
    For i = ll To ul Step 1
        If Application.WorksheetFunction.CountIf(CompareRange, i) = 0 Then
            RandUI42 = Round(ll + (ul - ll) * Rnd(), 0)
            Do While Application.WorksheetFunction.CountIf(CompareRange, RandUI42) > 0
                RandUI42 = Round(ll + (ul - ll) * Rnd(), 0)
            Loop
            Exit Function
        End If
    Next i
    RandUI42 = "#NOMORE"
End Function

any comments, suggestions, etc. are appreciated.
all questions will be answered.
hope someone will find it useful

chrees to all

I want to shuffle a pack of playing cards using excel

Hi everyone !

Can someody help me generate a sequence of numbers like
0
1
2 2
3 3 3
4 4 4 4
till 200

in excel with the help of a macro ??
My code is like this :

Code:

Sub Generate_Numbers()

 Dim CountRow As Long
 Dim cLastRow As Long
 Dim i As Double

 For CountRow = 1 To 201
 Worksheets("random numbers").Cells(CountRow, 1).Value = i
 i = i + 1
 Next CountRow

End Sub

Thank you !

Hello All,

I am working on a Random Cycle Count Generator that provides random SKU#s based on 3 separate columns of SKU listings. The user clicks a button to generate the SKU#s to cycle count for that day.

What I would like to see is a date stamp in the columns next(B,D,F) to the referenced SKU listing(A,C,E) based on which SKU#s are generated. This will let me see the last date that the SKU was generated. I would also like it to automatically save after generating.

I appreciate any help that you can provide. I looked on the forums for a while and couldn't find any obvious solution.

Thanks,
Thomas

hi guys, i m really confused could you help me pls!!
i ve got n integers (lets say n=5) how can i make excel/vba generate all possible combinations of 1, 2, 3 ... 5 digits from these n integers?

e.g. for n=5, the output should be

12345
1234
1235
...
123
124
125
...

12
13
14
15
23
...

1
2
3
4
5



thanks a lot!!!

Hi,

I run a lottery syndicate with nine people in who have all chosen two numbers each.

What I would like to have is a macro that randomly generates three lines of 6 numbers based on our 18 pre selected numbers i.e. use all 18 numbers with no number duplicated in any of the three lines generated (6 numbers per line).

Example numbers 2, 5, 7, 10. 11, 15, 17, 19, 20, 23, 24, 27, 29, 31, 32, 41, 44, 49,

Any assistance would be greatly appreciated

Many thanks in advance


Rob

N.B. Excel version 2003

Hello,

Can an expert in this forum devise of a VBA macro that creates 500 random numbers in cells A1:A500?

The numbers must all be odd and contain 3 digits in each cell... i.e.

135
179
977
etc...

many thanks!
andy

Hey, I was wondering if there was a way to generate random numbers in a series of cells without repeating one another.

For example, Say I want A1-D1 to be populated randomly with 1-6, not repeating a number at all. So i'd have a sequence like 132654 or 452136.

Is that possible to do?

I am trying to generate random numbers between 0 - 9 in ten cells that do not repeat an always include every value 0 - 9 (0, 1, 2, 3, 4,...9). The object is to have the numbers randomize each time. So cells A1:A10 could look like this:

3
9
4
5
1
0
6
8
7
2

Or, they could look like this:

9
7
5
2
8
4
1
3
6
0

I understand the rand and randbetween formulas, but am having trouble finding the logic to make them non-repeating, all inclusive and still random.

Any help? A bonus point for anyone who can figure out what this is for (hint: there is a kick-off in about four hours).

Dear Forum,

I am using the VBA code below (bottom) to return random numbers for a selected area of cells where the range of random numbers is between 1 and the
total number of cells. Unfortunately sometimes the random numbers appear more than once and I would like to amend this to ensure duplicates are not included, any help would be greatly appreciated, thanks, Matt

Ps I have already tried substituting with VBUniqRandInt() but it returns #NAME? in every selected cell??, i.e;

Code:

Sub RandomTable4()

For Each r In Selection
    r.Formula = "=int(VBUniqRandInt() *" & Selection.Count & "+1)"
Next
End Sub

Sub RandomTable4() ' but this returns duplicates

For Each r In Selection
    r.Formula = "=int(Rand() *" & Selection.Count & "+1)"
Next
End Sub