我有一个用户表单,用户可以在其中插入数据和数据将插入列B到M.我需要一个代码,无论是在工作表中还是在用户表单中自动填写以"RD 00001"开头的序列号,这将在每次数据进入时填写列a。
这背后的代码是非常简单的,专为您开始与Row 1
作为您的标题行空白表。它是动态的,所以基本上就是即插即用。只要用你所拥有的输入其他数据的代码调用sub。
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
注意:无论你找到你的最后一行输入其他数据,确保你的最后一行和这个子的最后一行是相同的