Automate creation of a userID in a table

[MarkA99]

I have a database/table of customers and a form for users to add customer details via a macro button. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I want to create a UID automatically by using the first five characters of the surname and the first character of the firstname followed by 01 and then increment if 01 has been used to 02 and if 02 has been used increment to 03 and so on. <o></o>
i.e.
James Brown = BROWNJ01<o></o>
Jonathan Brownson = BROWNJ02<o></o>
Jamie Brown = BROWNJ03…. etc<o></o>
<o></o>
The UID's are store in column A, the firstnames in B and surnames C.<o></o>
<o></o>
I'm having trouble with the find VBA command and counting the number of occurrences of a name after it has been added to the customer table.

Any help would be appreciated.<o></o>

 

[JoeMo]

Try this - assumes your surnames start in cell C2, adapt to suit:

Sub AssignUIDs()
Dim namRng As Range, lRw As Long, sUID As String

'Surnames in column C. Assume start at C2
lRw = Range("C" & Rows.Count).End(xlUp).Row
Set namRng = Range("C2", "C" & lRw)
For Each c In namRng
    Do
        i = i + 1
        sUID = Left(c.Value, 5) & Left(c.Offset(0, -1), 1) & "0" & i
        If WorksheetFunction.CountIf(Range("A2", c.Offset(0, -2)), sUID) = 0 Then
           c.Offset(0, -2).Value = sUID
           Exit Do
        End If
    Loop 
Next c
    
End Sub

 

[MarkA99]

Thanks JoeMo for your response. It is close to what I'm looking for.

The customers are entered as part of a DataForm and will be created at the botton of the table and sorted after the manipulation of the UID.

I don't need all the entries created only the new entry via the DataForm.

What I would ideally like is for the code to check for the occurance of a UID of say BROWNJ and count how many there are and then enter the incremental number, if there is only 1 then 01 but when it gets to 10 I don't want 010 entered.

Hope this makes sense.

 

[JoeMo]

You should be able to adapt the code I posted and it needs to be tweaked to make it more robust. For example, if the surname has fewer than 5 letters what format should the UID take?

 

[MarkA99]

Hi JoeMo

I worked out the code finally that works in my situation as follows:

----------------------------------------------------------
Do Until ActiveCell = ""
sUID = UCase(Left(ActiveCell.Value, 5)) & Left(ActiveCell.Offset(0, -1), 1)
ActiveCell.Offset(0, -2).Value = sUID
UIDc = WorksheetFunction.CountIf(Range("A:A"), sUID & "*")
ActiveCell.Offset(0, -2).Value = sUID

If WorksheetFunction.CountIf(Range("A:A"), sUID & "*") = 0 Then
ActiveCell.Offset(0, -2).Value = sUID & "01"
vDate = Now
ActiveCell.Offset(0, 15).Value = vDate
ActiveCell.Offset(0, 16).Value = vDate
Else
If WorksheetFunction.CountIf(Range("A:A", ActiveCell.Offset(0, -2)), sUID & "*") > 9 Then
UIDc = WorksheetFunction.CountIf(Range("A:A", ActiveCell.Offset(0, -2)), sUID & "*")
ActiveCell.Offset(0, -2).Value = sUID & UIDc
vDate = Now
ActiveCell.Offset(0, 15).Value = vDate
ActiveCell.Offset(0, 16).Value = vDate
Else
ActiveCell.Offset(0, -2).Value = sUID & "0" & UIDc
vDate = Now
ActiveCell.Offset(0, 15).Value = vDate
ActiveCell.Offset(0, 16).Value = vDate
End If
End If
End If
Selection.Offset(1, 0).Select
Loop
----------------------------------------------------------------------

What I'm having trouble with now is entering the date/time (now) to a record that is amended via the ActiveSheet.ShowDataForm VBA command, so it reflects the modifed date. Do you know how to reference the amended record and go the the last column in the table and enter the date/time. Column 18 is the date created cell and 19 the modified cell

Thanks again