Well, I tried your code and it works only partly. When you add the first
entry it will assign the number XX001, however when you go to inset the
second one it will not assign a number at all. The way I am trying to make it
work is the number as I call it will be a 2 letter alphanumeric text with a 3
digit number counter at the end starting with number 000 and counting up. I
am trying to get it to compare what is selected in the combobox 'CboDept'
with the first 2 letters of the data in column 'M' of the sheet named
'ProCode' and either add 1 number to what matches 'CboDept' or create a new
number starting back at 000 with the new 2 letters selected and then apply it
to the added data when the add button is clicked.

"Steve Yandl" wrote:

On giving it some more thought, there is an error condition I didn't
consider when writing the original sub. Suppose you already have values
xyz001, xyz002, xyz003 and then instead of entering xyz, you simpy entered
xy. Ideally, this would generate xy001 but since there are already matches
for xy, the sub will try to treat z003 as a number and generate an error.

The corrected version below should handle that. I also changed things so
the final result is assigning an appropriate alpha numeric value to your
variable dept rather than insert the value at the bottom of column M. I
think the code below should integrate easily with your existing code.

_______________________________________

Dim intMMtop As Integer
Dim dept As String
Dim Y As Integer
Dim X As Integer

intMMtop = Worksheets("Lists").Range("M65536").End(xlUp).Row
dept = Me.CboDept.Text
Y = 0

For R = 1 To intMMtop
strCell = Worksheets("Lists").Cells(R, 13).Value
If InStr(strCell, dept) = 1 And _
IsNumeric(Mid(strCell, Len(dept) + 1)) Then
X = CInt(Mid(strCell, Len(dept) + 1))
If X Y Then
Y = X
End If
End If
Next R

dept = dept & Format(Y + 1, "00#")
_______________________________________

Steve