[Excel]VBA自定义BOM

闲话少说,上代码。

新建模块,粘贴如下代码。

	Option Explicit	
	Function SerNo(A, B As Integer)
	Dim T1 As Single
	Dim T2 As Single, T2a
	Dim T3 As Single
	Dim T4 As Single
	Dim T5 As Single
	Dim T6 As Single
	A = A & "-0-0-0-0-0-0-0-0-0-0-0"
T1 = InStr(A, "-")
T2 = InStr(Replace(A, "-", "@", T1 + 1, 1, 1), "@") + T1
T3 = InStr(Replace(A, "-", "@", T2 + 1, 1, 1), "@") + T2
T4 = InStr(Replace(A, "-", "@", T3 + 1, 1, 1), "@") + T3
T5 = InStr(Replace(A, "-", "@", T4 + 1, 1, 1), "@") + T4
T6 = InStr(Replace(A, "-", "@", T5 + 1, 1, 1), "@") + T5 Select Case B
Case 1 '执行第一阶
  SerNo = Mid(A, 1, InStr(A, "-") - 1)
Case 2 '执行第二阶
SerNo = Mid(A, 1, T1) & CStr(Mid(A, T1 + 1, T2 - T1 - 1)) + 1
Case 3 '第三阶
SerNo = Mid(A, 1, T2) & CStr(Mid(A, T2 + 1, T3 - T2 - 1)) + 1
Case 4 '第四阶
SerNo = Mid(A, 1, T3) & CStr(Mid(A, T3 + 1, T4 - T3 - 1)) + 1
Case 5 '第五阶
SerNo = Mid(A, 1, T4) & CStr(Mid(A, T4 + 1, T5 - T4 - 1)) + 1
Case 6 '第六阶'
SerNo = Mid(A, 1, T5) & CStr(Mid(A, T5 + 1, T6 - T5 - 1)) + 1
End Select End Function

在单元格中,输入=serno(A1,2),如此炮制。

分享到: