Forum Discussion
Random alphanumeric generation in a cell
Hi everyone.
A bit of a newbie when it comes to Excel here.
What I'm trying to achieve is a means of automatically generating an alphanumeric string, lets say 7 long, when clicking on a cell. The problem lies insofar as the cell itself can change due to the document having revisions and the each revision to the document will require a new number to be generated in the same row but one cell down. The first instance would be C9, the next one would require a new random sequence in C10 and so on.
My question is twofold:
- How can I achieve this? And,
- If a macro is able to do this, how do I have that macro automatically generate into the cell?
<edit>
Might be able to get away with using the same number in the row, so just creating that random sequence once would be enough as I can then pull the data from the cell and duplicate when required.
Kindest regards,
Jim
5 Replies
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 ThenRandomize
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)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
