Modifying VBA Code to generate 4-digit numbers...

[hararahm]

I'm looking for assistance with modifying the following VBA code; this code auto-generates 4-digit unique numbers, using zero as one of the starting digit as well. I need the 4-digit numbers NOT to begin with a zero, the 4-digit numbers should only begin with numbers 1-9.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Test As Integer
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
For Each Cell In Target
With Cell.Offset(0, 1)
If IsEmpty(Cell) Then
.ClearContents
Else
.NumberFormat = "0000"
Do
.Value = Int(9999 * Rnd())
Test = WorksheetFunction.CountIf(.EntireColumn, .Value)
Loop Until Test <= 1
End If
End With
Next Cell
Application.EnableEvents = True
End Sub

 

[ChrisM]

I'm not sure what you want the low range of your numbers to be then. If you don't want to start with a leading zero then I assume you want a random number between 100 and 9999? If so, then change

.Value = Int(9999 * Rnd())
to
.Value = Int((9999 - 100 + 1) * Rnd() + 100)

If that is wrong just change 100 to whatever the lowest number should be.

 

[hararahm]

You're a genius!
Thank you!