Random number, generates only once, 4 digits!

[wpryan]

I'm trying to generate a series of random numbers. the criteria is that the numbers should only be generated once, and must have 4 digits (e.g. between 0000 and 9999).
I've tried the following in Excel:

=TRUNC((RAND()*(9999-0)+0),4)

but it still will show a number less than 1000 as 3 digits (or less than 100 as 2 etc), not 4. Also, it calculates with every change to the form.
Would anyone care to help? thanks.

 

[wpryan]

Here it is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B1") <> "" Then
    Range("A1").Value = CInt(((9999 - 1000) * Rnd) + 1000)
Else:
    Range("A1") = ""
End If
End Sub

as you can see if any other value in the worksheet is changed it will recalculate a new number...

 

[kpark91]

Try This:

[color=darkblue]Dim[/color] B_1 [color=darkblue]As[/color] [color=darkblue]String[/color]
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    
    [color=darkblue]Call[/color] Test
    B_1 = Range("B1").Value
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[color=darkblue]Sub[/color] Test()
    
    [color=darkblue]If[/color] Range("B1").Value <> B_1 [color=darkblue]Then[/color]
        [color=darkblue]If[/color] Range("B1").Value <> "" [color=darkblue]Then[/color]
            Range("A1").Value = [color=darkblue]CInt[/color](((9999 - 1000) * Rnd) + 1000)
        [color=darkblue]Else[/color]
            Range("A1").Value = " "
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] Sub

 

[sulakvea]

are we talking about unique rand numbers

 

[kpark91]

What are unique random numbers?
If you mean random numbers in a range..
Yes, we are. The range is 1000~9999

 

[wpryan]

That works great. Thanks a lot!

 

[vishalvrathod]

But that works for only one row that is A1 and B1

 

[kpark91]

So, what's your question?

 

[vishalvrathod]

should work for all rows ..thats my question