Let me try and explain it more clearly. I have a userform with a combobox on
it which gets it values from sheet 'Lists' C2:C10. I would like when the user
selects the value in the combobox and click the add button it will create the
alphanumeric number in column 'M' on sheet 'ProCode', it will search for the
left 2 letter and add 1 to the numerical value.

I tried you code and I have 2 problems

#1 I am not sure how to reference sheet 'Lists' column C2:C10 in the
following line

Prefix = left(range(??).value, 2)

#2 I keep coming up with a compile error for the folling line with the
replace highlighted

highestnumber = highestnumber + value(replace(range("M" &
X).value), prefix, ""))

"Ross Culver" wrote:

Your description of what you want is not very clear; however, I think this
is it.

Dim X as integer, Prefix as string, HighestNumber as long, ValueDesired as
string
X = 2 'Assuming that your values in col M start at row 2
Prefix = left(range(??).value, 2) 'Use the range to which you set the
comboboxes linked cell. That's where the selected value is stored.
HighestNumber = 0

Do while range("M" & x).value < ""
If left(range("M" & x).value, 2) = prefix then
highestnumber = highestnumber + value(replace(range("M" &
X).value), prefix, ""))
End if
x = x + 1
loop

ValueDesired = prefix & highestnumber + 1

Try this.

Ross

"Mekinnik" wrote in message
...
I am trying to create an alphanumeric value and insert it into a sheet
column
'M' based on a combocox selection, I want it to search column 'M' find the
left 2 letters then add 1 number to the value it creates. I have been
trying
to create it but keep failing, here is my code

'searches for duplicate MSDS# in column 'M'
dept = Columns(13).Find(What:=CboDept.Value, _
After:=Cells(5000, 13), LookIn:=xlFormulas, _
LookAT:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Row
If Me.CboDept.Value = "" Then
End If
If Me.CboDept.Value = dept Then
dept = Me.CboDept.Value & 0 + 1
End If
Exit Sub