|
上面的两个工作表，在出库单工作表输入内容后，点右面的+号，则自动生成新的单号，这个单号和E1单元格也有关联，就是当E1单元格显示出库清单，则取出库的首字母CK+当前日期231108+出库明细表里的单号判断，从001开始生成。退库清单则，TK+231108+判断序号这样的话，就可以知道每一种类型的票据当天排到了多少号，就看后三位即可。
Dim selectedType As StringDim lastCode As StringDim newCode As String ' 获取E1单元格选中的出库类型selectedType = Range("E1").value ' 根据出库类型确定新的单号的命名规则Select Case selectedType Case "出 库 清 单" arr = Sheets("出库明细").[a1].CurrentRegion Str1 = "CK" & Format(Date, "yymmdd") For i = UBound(arr) To 2 Step -1 If InStr(arr(i, 3), Str1) Then code = Format(Right(arr(i, 3), 3) + 1, "000") Exit For Else code = "001" End If Next newCode = "CK" & Format(Date, "yymmdd") & code Case "退 库 清 单" arr = Sheets("出库明细").[a1].CurrentRegion Str1 = "TK" & Format(Date, "yymmdd") For i = UBound(arr) To 2 Step -1 If InStr(arr(i, 3), Str1) Then code = Format(Right(arr(i, 3), 3) + 1, "000") Exit For Else code = "001" End If Next newCode = "TK" & Format(Date, "yymmdd") & code Case "转 移 清 单" arr = Sheets("出库明细").[a1].CurrentRegion Str1 = "ZY" & Format(Date, "yymmdd") For i = UBound(arr) To 2 Step -1 If InStr(arr(i, 3), Str1) Then code = Format(Right(arr(i, 3), 3) + 1, "000") Exit For Else code = "001" End If Next newCode = "ZY" & Format(Date, "yymmdd") & code Case "入 库 清 单" arr = Sheets("出库明细").[a1].CurrentRegion Str1 = "RK" & Format(Date, "yymmdd") For i = UBound(arr) To 2 Step -1 If InStr(arr(i, 3), Str1) Then code = Format(Right(arr(i, 3), 3) + 1, "000") Exit For Else code = "001" End If Next newCode = "RK" & Format(Date, "yymmdd") & code Case "盘 点 库 存" arr = Sheets("出库明细").[a1].CurrentRegion Str1 = "PD" & Format(Date, "yymmdd") For i = UBound(arr) To 2 Step -1 If InStr(arr(i, 3), Str1) Then code = Format(Right(arr(i, 3), 3) + 1, "000") Exit For Else code = "001" End If Next newCode = "PD" & Format(Date, "yymmdd") & code Case Else ' 如果E1单元格没有选中任何选项，清空I3单元格 Range("I3").value = "" Exit SubEnd Select Range("I3").value = newCode
|
