Excel vba increment unique id for each row

[Jaye Cavallo]

Hello,

I am seeking an Excel VBA method to create a unique ID for every row of data that is entered by an attendee to an event. I have the userform designed and the data from the form is passed on to the worksheet as expected. Is there a way to create a unique id every time an attendee enter his information and the data is saved to the work sheet?

 

[Jaye Cavallo]

There are three events, one in each county. B for one county, C for the second county and F for the third county. So each event in each county would represent an attendee with not only a unique number, but also a number that would show the county for which they are from, thus the letter. For the first event, the ID would B000000. The second event would be C000000. The third event would be F000000. So, one Excel file to register attendees at the first event, the second file for the second event, and a third file for the third event, just revising the VBA code to reflect the letter change in the ID and increment the number by one for each attendee.

 

[Akuini]

Ok, I will try to write the code in few hours when I have time.
When you update data via the userform, will the last cell with data in column L always have the highest number ID?
If so, the code can generate a new ID just by adding 1 to the number ID in the last cell.

 

[Jaye Cavallo]

Yes when the data is added the column L will increment by 1

 

[Akuini]

Yes when the data is added the column L will increment by 1

Try this:
In your code, add the blue lines before the End Sub

Cells(emptyRow, 9).Value = TextBox9.Value
Cells(emptyRow, 10).Value = TextBox10.Value

'ID pattern "?000001" 'a letter followed by 6 digits.
With Cells(emptyRow, "L")
    .Value = Left(.Offset(-1), 1) & Format(Mid(.Offset(-1), 2) + 1, "000000")
End With

End Sub

In order to make it work you need to have at least 1 row of data (say in row 2) and manually insert the ID, such as B000001

Jaye Cavallo - Excel vba increment unique id for each row #1.xlsm
	K	L
1	something	ID
2	qwert	B000001
3
Sheet1

Basically, the code will find the last cell with data in col L (i.e the last ID) and then insert a new ID below it. The ID pattern must be a letter followed by 6 digits.

 

[Jaye Cavallo]

Thank you for your solution. The code works a you suggested. Now I will be pushing the envelop. In my next post, I will ask if it is possible to print a name tag from this userform.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.