[Solved] VBA code to auto serial number in column A after my userform entered data in column B

by


The code behind this is very simple and designed for you to start on a blank sheet with Row 1 being your header row. It’s dynamic so essentially plug and play. Just call on the sub with whatever code you have for entering in the other data.

Sub SerialCode()
    Dim ws As Worksheet
    Dim lastSerial, digits, i As Integer
    Dim nextRow, lastRow As Long
    Dim newSerial As String

    Set ws = ThisWorkbook.Sheets(1)
    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, "RD ", ""))
    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 = "RD " & newSerial & lastSerial 'concatenate the serial code
    ws.Range("A" & nextRow).Value = newSerial
End Sub

NOTE: whatever you have for finding your last row to input the other data, make sure your last row and this sub’s last row are the same.

7

solved VBA code to auto serial number in column A after my userform entered data in column B