|
上面的兩個(gè)工作表，在出庫(kù)單工作表輸入內(nèi)容后，點(diǎn)右面的+號(hào)，則自動(dòng)生成新的單號(hào)，這個(gè)單號(hào)和E1單元格也有關(guān)聯(lián)，就是當(dāng)E1單元格顯示出庫(kù)清單，則取出庫(kù)的首字母CK+當(dāng)前日期231108+出庫(kù)明細(xì)表里的單號(hào)判斷，從001開始生成。退庫(kù)清單則，TK+231108+判斷序號(hào)這樣的話，就可以知道每一種類型的票據(jù)當(dāng)天排到了多少號(hào)，就看后三位即可。
Dim selectedType As StringDim lastCode As StringDim newCode As String ' 獲取E1單元格選中的出庫(kù)類型selectedType = Range("E1").value ' 根據(jù)出庫(kù)類型確定新的單號(hào)的命名規(guī)則Select Case selectedType Case "出 庫(kù) 清 單" arr = Sheets("出庫(kù)明細(xì)").[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 "退 庫(kù) 清 單" arr = Sheets("出庫(kù)明細(xì)").[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 "轉(zhuǎn) 移 清 單" arr = Sheets("出庫(kù)明細(xì)").[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 "入 庫(kù) 清 單" arr = Sheets("出庫(kù)明細(xì)").[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 "盤 點(diǎn) 庫(kù) 存" arr = Sheets("出庫(kù)明細(xì)").[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單元格沒(méi)有選中任何選項(xiàng)，清空I3單元格 Range("I3").value = "" Exit SubEnd Select Range("I3").value = newCode
|
