code for universal serial number in excel vba userform

[Vishwanath Korgaonkar]

Hi-
I have 6 department which I select from combobox1

If I select department as Press shop the present code gives serial no as PS 000001 ,if Machine shop then MS 000002 where both codes are separate

My present code is as follows which I copied from one website

Sub serialno_MS()
Dim ws As Worksheet
    Dim lastSerial, digits, i As Integer
    Dim nextRow, lastrow As Long
    Dim newSerial As String
 Set ws = ThisWorkbook.Sheets("DATA")
nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1 'Finds the last row in A
    lastrow = nextRow - 1
    newSerial = "" 'set value of our string blank
If (nextRow - 1) < 2 Then 'If statement to catch if there's only a header
        lastSerial = 0
    Else: lastSerial = CInt(Replace(ws.Range("A" & lastrow).Value, "MS", ""))
    End If

    lastSerial = lastSerial + 1
    digits = 6 - Len(lastSerial) 'how many 0's are there

    For i = 1 To digits
        newSerial = newSerial & "0" 'start building the string with 0's
    Next i

    newSerial = "MS " & newSerial & lastSerial 'concatenate the serial code
    ws.Range("A" & nextRow).Value = newSerial
End Sub

I am looking for universal code if I take entry for Machine shop it should take last number of Machine shop entry and continue ie.MS 000001,if selected as Press shop it should take last entry number of Press shop and continue i.e "PS 000001"

can any one help?

 

[Peter_SSs]

When posting vba code please use the available code tags to implement/preserve formatting. My signature block below has more details. I have added the tags for you this time.

 

[Vishwanath Korgaonkar]

Hi-
I have 6 department which I select from combobox1

If I select department as Press shop the present code gives serial no as PS 000001 ,if Machine shop then MS 000002 where both codes are separate

My present code is as follows which I copied from one website

Sub serialno_MS()
Dim ws As Worksheet
Dim lastSerial, digits, i As Integer
Dim nextRow, lastrow As Long
Dim newSerial As String
Set ws = ThisWorkbook.Sheets("DATA")
nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1 'Finds the last row in A
lastrow = nextRow - 1
newSerial = "" 'set value of our string blank
If (nextRow - 1) < 2 Then 'If statement to catch if there's only a header
lastSerial = 0
Else: lastSerial = CInt(Replace(ws.Range("A" & lastrow).Value, "MS", ""))
End If

lastSerial = lastSerial + 1
digits = 6 - Len(lastSerial) 'how many 0's are there

For i = 1 To digits
newSerial = newSerial & "0" 'start building the string with 0's
Next i

newSerial = "MS " & newSerial & lastSerial 'concatenate the serial code
ws.Range("A" & nextRow).Value = newSerial
End Sub

I am looking for universal code if I take entry for Machine shop it should take last number of Machine shop entry and continue ie.MS 000001,if selected as Press shop it should take last entry number of Press shop and continue i.e "PS 000001"

can any one help?