Insert random number only if cell empty

  • Thread starter Thread starter M.L.
  • Start date Start date
M

M.L.

Hello. I'd like my spreadsheet to place a 7 digit random number into a
cell only the cell is empty, otherwise leave the contents as is. I
simply don't want the number to change if one is already present.

I currently keep the number static by pressing F9 when I enter the
RAND formula, but that forces me to manually change the number when I
save the spreadsheet to another name. I'd like the spreadsheet to
automatically calculate a new number in the cell as soon as I erase
the previous number in that cell. Any help on this issue will be
appreciated. Thanks.
 
M.L. said:
I'd like my spreadsheet to place a 7 digit random number into a
cell only the cell is empty, otherwise leave the contents as is.
Click to expand...

In the VBAProject "Sheet1" object (or whatever the name is), add or modify
the following event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("a1")) Is Nothing Then
'range("a1") is included in the changed range
If Range("a1") = "" Then
Range("a1").NumberFormat = "0000000"
Range("a1") = Int(1E8 * Evaluate("rand()"))
End If
End If
Application.EnableEvents = True
End Sub


This does what you ask for ("only [when] the cell is empty"). Note that
this can be defeated if the user puts something else into the cell.


Note: I use Evaluate("rand()") instead of the VBA Rnd function for a number
of reasons. If you prefer to use Rnd, it would be prudent to place a call
to Randomize in a Workbook_Open event macro or to add the following to the
macro above:

Static notFirst As Integer
If notFirst = 0 Then Randomize: notFirst = 1


----- original message -----
 
Back
Top