Hello,
So I work at a military school and need to random generate non-repeating numbers for test purposes. I found a macro that does what I need but it wont auto recalculate. I have tried adding in the Application.volatile but it does nothing for me. I know there are other ways of running the macro like a button and such but I need to simplify its use as much as possible for the less fortunate in the office who will use it. I will post the code below.
Thank you
Adam
Sub DrawNumbers()
'If you want unique random numbers, i.e. you want to shuffle the numbers 1 to 4
'
Dim i, choice, balls(1 To 4)
Dim lngArr(1 To 4) As Long
Dim RwNdx1 As Long
Dim RwNdx2 As Long
Dim ColNdx As Long
Dim ColW As Long
Dim lrow As Long
Dim cnt1 As Long
Dim cnt2 As Long
Dim temp As Long
Dim Rng As Range
Dim ar As Range
Dim Cell As Range
ColW = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
' Clear the existing data first
Range("A1", Cells(lrow, ColW)).ClearContents
Range("A1").Select
cnt1 = InputBox("How many sets of numbers do you want?")
cnt2 = InputBox("How many numbers in each set do you want?")
If cnt2 > 4 Then
Call DrawNumbers
End If
RwNdx1 = 2
RwNdx2 = cnt2 + 1
For ColNdx = 1 To cnt1
Randomize
For i = 1 To 4
balls(i) = i
Next
For i = 1 To 4
choice = 1 + Int((Rnd * (4 - i)))
temp = balls(choice)
balls(choice) = balls(5 - i)
balls(5 - i) = temp
Next
i = 0
With Cells(RwNdx1 - 1, ColNdx)
.Value = "Set" & ColNdx
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With
Bookmarks