Random number generator which will generate numbers in selected area of the spreadsheet.

Sub randomNumbers()
	Dim Low As Long, high As Long
	Dim cell As Range, rndNumber As Long, x As Long
	Low = 1
	high = Selection.Rows.Count
	If Selection.Rows.Count <> Selection.Columns.Count Then
		MsgBox "Invalid selection"
		Exit Sub
	End If
getmeout:
	Selection.Clear
	For Each cell In Selection.Cells
		x = 0
		'If WorksheetFunction.CountA(Selection) = (high - Low + 1) Then Exit For
		Do
			rndNumber = Int((high - Low + 1) * Rnd() + Low)
			x = x + 1
			If x = 1000 Then GoTo getmeout
		Loop Until Intersect(Selection, Rows(cell.Row)).Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing 		And _
		Intersect(Selection, Columns(cell.Column)).Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
		cell.Value = rndNumber
	Next
End Sub

Categorized in:

VBA Excel,

Last Update: May 18, 2024