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

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

Mekinnik,

Here try it with this code.

= dept Then


Sub Mekinnik()

Dim iLastRow As Long
Dim look4Me As String
look4Me = Me.CboDept.Value
iLastRow = Range("H" & Range("H:H").Rows.Count).End(xlUp).Row
'change to the column you want to search

Range("H1").Select 'go to the top of the column to begin searching

Do Until iLastRow = -1

If ActiveCell = look4Me Then
dept = Me.CboDept.Value & 0 + 1
Else

End If

iLastRow = iLastRow - 1
ActiveCell.Offset(1, 0).Select
Loop

End Sub

"Mekinnik" wrote:

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

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

"Mekinnik" wrote in message
...
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)

Did you set a LinkedCell value for your combobox? If so, then reference it
like this: sheets("Lists").range("D2").value
D2 is just an example. Use which ever cell you actually set the LinkedCell
value to.
If you didn't set the linkedcell value then use:
Prefix = me.combobox.selectedtext

What's in C2? Just the two letter codes or something more? And the new
value that you're trying to derive, it that supposed to added to the bottom
of your List?


#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, ""))

Actually, that wouldn't get you what you want anyway. Try this:
dim NextValue as long
NextValue = 0

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

Note the change in the formula above. I had too many parenthesis in the
last one.


"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