Forum Discussion
Random alphanumeric generation in a cell
8,
JJ,
Right-click the sheet tab, choose View Code and paste the following into the large white window...
'---
'=INT((High - Low + 1) * Rnd + Low) generates a random number between High and Low
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rw As Long
If Target.Address = Me.Range("C8").Address Then
Randomize
Rw = Me.Cells(Me.Rows.Count, 3).End(xlUp).Row + 1
Me.Cells(Rw, 3).Value2 = VBA.Int((9999999 - 1000001 + 1) * Rnd + 1000001)
Cancel = True
End If
End Sub
'---
Make sure cell C8 contains some text/data - "Double-Click" - would be informative.
Double-click cell C8 whenever you need a new random number added to a blank cell below C8.
Jim Cone
https://goo.gl/IUQUN2 (Dropbox)
"8," is a typing error
"low" should follow "and", all on the same line
"Boolean)" should follow "as", all on the same line.
Jim Cone
That's brilliant sir, you're a star!
Now, is there a way to have it generate a number into C8 and ONLY if the field is empty in C8 to prevent new numbers being generated when the field has a value?
After that, I should be able to copy that data into subsequent fields
'Shorter Range("C8") version
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = Me.Range("C8").Address Then
If VBA.Len(Target.Value) < 1 Then
Randomize
Target.Value2 = VBA.Int((9999999 - 1000001 + 1) * Rnd + 1000001)
End If
Cancel = True
End If
End SubThanks again-that's perfect.
Kindest regards,
Jim
