在我的用户表单在 B 列中输入数据后，VBA 代码在 A 列中自动序列号 [英] VBA code to auto serial number in column A after my userform entered data in column B

问题描述

我有一个用户表单，用户可以在其中插入数据，数据将插入列 B 到 M.我需要一个代码，无论是在工作表中还是在用户表单中，以自动填充以RD 00001"开头的序列号，它将填充列A 每次数据输入.请有人给我一个想法.

I have a userform in which user can insert data and data will insert in column B to M. I need a code, either in worksheet or in userform to auto fill serial number starting with "RD 00001" which will fill in column A everytime data has enter. Please someone give me an idea.

推荐答案

这背后的代码非常简单，旨在让您从一张白纸开始，Row 1 作为标题行.它是动态的，因此基本上即插即用.只需使用您输入其他数据的任何代码调用 sub.

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.

这篇关于在我的用户表单在 B 列中输入数据后，VBA 代码在 A 列中自动序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！