I was gonna add some validation checks, but then decided not to.

This function should work ok, too:

Option Explicit
Function UseThisOne(myStr As String, RngToCheck As Range) As Long

Dim iCtr As Long
Dim res As Variant

UseThisOne = 0
For iCtr = 1 To 999
res = Application.Match(myStr & Format(iCtr, "000"), RngToCheck, 0)
If IsError(res) Then
Exit For
Else
UseThisOne = iCtr
End If
Next iCtr

UseThisOne = UseThisOne + 1

End Function



Dave Peterson wrote:

I didn't take the time to set up a userform with all those controls, but this
kind of thing worked for me.

Option Explicit
Function UseThisOne(myStr As String, RngToCheck As Range) As Long

Dim iCtr As Long
Dim res As Variant

UseThisOne = -999
For iCtr = 1 To 999
res = Application.Match(myStr & Format(iCtr, "000"), RngToCheck, 0)
If IsError(res) Then
Exit For
Else
UseThisOne = iCtr
End If
Next iCtr

If UseThisOne = -999 Then
'it wasn't found
UseThisOne = 1
Else
UseThisOne = UseThisOne + 1
End If

End Function
'and just to show that it works...
Sub testme()

Dim ws As Worksheet
Dim RngToCheck As Range
Dim DestCell As Range
Dim NextOneToUse As Long
Dim myStr As String

myStr = "MS" 'my version of your me.cbodept.value

Set ws = Worksheets("procode")
With ws
Set RngToCheck = .Range("m1", .Cells(.Rows.Count, "M").End(xlUp))
Set DestCell = .Cells(.Rows.Count, "M").End(xlUp).Offset(1, 0)
End With
NextOneToUse = UseThisOne(myStr, RngToCheck)
DestCell.Value = myStr & Format(NextOneToUse, "000")

'your code may look like
'NextOneToUse = UseThisOne(me.cbodept.value, RngToCheck)
'DestCell.Value = me.cbodept.value & Format(NextOneToUse, "000")

End Sub

Mekinnik wrote:

this is my current code for my button click event, I have a number of things
I want to happen when the button is clicked and one protion is giving me
trouble. The part that creates the MSDS#. I want the value of CboDept (a list
of up to 3 letters) to be converted to an alphanumeric number (i.e. xx001)
and store it into a string so I can call it at the end of the code to be
inserted into sheet "Lists" column 'M'. Everytime the button click event
fires I want the code to search column 'M' for the left 2 or 3 letters that
match the CboDept selection and then add onto it the next sequencial number
starting at 001 and so on. If it doesn't find a match then it will just add
it to the column.
#1) I cannot seem to get the 001 at the end of the 2 letters I just get a 1
and it won't add to the count.
#2) I am unable to get it to work in conjuction with the current code
#3) I do believe I am going about it all wrong, please help to steer me in
the right direction

Private Sub BtnAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim dept As String
Dim c As Range

Set ws = Worksheets("ProCode")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'creates the MSDS#
dept = Me.CboDept.Value & "0" + 1
For Each c In Worksheets("Lists").Range("M2:M1000")
If c.Value < dept Then
c.Value = dept
End If
If c.Value = dept Then
MsgBox "number matched"
Exit Sub
End If
Next c

'check for the product name
If Trim(Me.TxtProd.Value) = "" Then
Me.TxtProd.SetFocus
MsgBox "Please enter the product name"
Exit Sub
End If

'copy the data to the database
Application.EnableEvents = False
ws.Cells(iRow, 2).Value = Me.TxtProd.Value
ws.Cells(iRow, 3).Value = IIf(Me.CkBox1.Value, "Yes", "No")
ws.Cells(iRow, 4).Value = IIf(Me.CkBox2.Value, "Yes", "No")
ws.Cells(iRow, 5).Value = IIf(Me.CkBox3.Value, "Yes", "No")
ws.Cells(iRow, 6).Value = Me.CboFire.Value
ws.Cells(iRow, 7).Value = Me.CboHealth.Value
ws.Cells(iRow, 8).Value = Me.CboReact.Value
ws.Cells(iRow, 9).Value = Me.CboSpec.Value
ws.Cells(iRow, 10).Value = Me.CboDisp.Value
ws.Cells(iRow, 11).Value = Me.TxtQuan.Value
ws.Cells(iRow, 12).Value = Me.TxtDate.Value
ws.Cells(iRow, 13).Value = dept
Application.EnableEvents = True

'the sort will fire with this line.
ws.Cells(iRow, 1).Value = Me.CboMan.Value
FrmProduct.CboMan.Value = Me.CboMan.Value

'clear the data
Me.CboMan.Value = ""
Me.TxtProd.Value = ""
Me.CkBox1.Value = False
Me.CkBox2.Value = False
Me.CkBox3.Value = False
Me.CboFire.Value = ""
Me.CboHealth.Value = ""
Me.CboReact.Value = ""
Me.CboSpec.Value = ""
Me.CboDisp.Value = ""
Me.TxtQuan.Value = ""
Me.TxtDate.Value = ""
End Sub

--

Dave Peterson

--

Dave Peterson