目前使用COUNTIFS函數公式可以達到流水號與分號不重號的功能
但因為公式很長,運行計算時會花比較久的時間,自己也曾試著用VBA來計算,
可是找了網路上很多資料都沒有作出來,自己本身是沒有學過VBA代碼的,想請問有大大能幫忙嗎?
主要就是能依據填入的內容作出比對後,讓編號的流水號不重號,如果比對後有相同流水號,分號就要自動跳號(從0開始,A、B、C以此類推,跳過英文I和O),不能完全一樣!
目前使用的流水號判別公式:=IF($D$2="","",IF(訂單總表!$C$3="","001",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=0,TEXT(訂單編碼生成登記表!$C$9,"000"),IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))>0,TEXT(訂單編碼生成登記表!$C$9,"000")))))
目前使用的分號判別公式:=IF($D$2="","",IF(訂單總表!$C$3="","N/A",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=0,"N/A",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=1,"0",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=2,"A",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=3,"B",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=4,"C",IF(COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2)=5,"D",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=6,"E",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=7,"F",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=8,"G",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=9,"H",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=10,"J",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=11,"K",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=12,"L",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=13,"M",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=14,"N",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=15,"P",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=16,"Q",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=17,"R",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=18,"S",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=19,"T",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=20,"U",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=21,"V",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=22,"W",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=23,"X",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=24,"Y",IF((COUNTIFS(訂單總表!$C3:$C200000,$B$5,訂單總表!$C3:$C200000,$B$4,訂單總表!$C3:$C200000,$B$3,訂單總表!$C3:$C200000,$B$2))=25,"Z"))))))))))))))))))))))))))))
實際資料非常多,圖片中的只是舉例說明,若有不清楚的部分可以問我,謝謝大家。
Bobo0509菩薩慈悲:
末學駑鈍,實在不知道您究竟的需求及操作流程是怎樣,怕不合式,又得改寫對應的流程和架構。我原本是設計讓程式自動填入(或提示填入)可以用的新編號,可是您似乎只是要提示使用者已編編號編到哪裡?是麼?然後再讓使用者自行輸入。然而程式又當在什麼時機知道使用者已輸入完畢呢?是輸入完流水號後嗎?所以使用者是不必管分單號囉?由程式自行決定麼?如果真是這樣,那分單號與流水號為什麼要用兩套不同的機制輸入呢?(要嘛是都可使用者輸入,要嘛就是都由程式帶入。我本以為是這樣的邏輯,所以原來寫的才是均可由使用者輸入的。但看您的表單好像不是這樣。)因為這關係到程式要何時才能更新現有編號的記錄,才能正確掌握編號重不重複的問題(詳所附程式碼流程邏輯),且理論上講這一更新後舊有的記錄就不應當再有變動了。……(我剛才睡起才想到,也有一種可能是要使用者可以輸入流水號,但不能輸入分單號。在使用者輸入完流水號後,才由程式自動賦予不重號的分單號,是這樣嗎???這樣要怎麼確定使用者真的輸入完流水號,而不再改變了呢?還是希望使用者可以輸入改變,且在有重號時出現警示???(我看您表單一圖的分單號處字是紅色、底非白的緣故)這些,都要末學再一一打字詢問嗎?(究竟是您有亟需還是末學,真是有點搞不太清楚狀況了,真有點皇上不急急死太監的感覺。)末學真的不知菩薩您的情境究竟是哪一種,故無法確實再實作下去了)
Anyway,因為怕寫了又要改寫,我就先把目前改作的貼過來回答了。因為分單號的部分仍搞不清楚狀況,就僅實作了顯示目前編到流水號、分單號幾號的機制。但這樣我實在不知該何時才好更新現有的編號記錄,怕我想的又和菩薩您的有出入,就只好先打住了。若仍是無法契合您的所需,就請菩薩您參考現有程式的原理,自己改寫了。感恩感恩 南無阿彌陀佛
末學會在 Google Meet Standby 有問題或要進一步說明可隨時叩我,我人不在才用打字的,若打字回應我若無法再即時回覆,當可諒原矣。感恩感恩 南無阿彌陀佛
'流水號欄位索引值
Const streamingColumnIndex As Byte = 5
'分單號欄位索引值
Const subListColumnIndex As Byte = 6
'記下新的編號
Dim newNumberField As String
Private Sub Worksheet_Change(ByVal target As Range)
If target.Text = "" Then Exit Sub
Select Case target.Column
'流水號前一欄(D欄)輸入後
Case 4
If target.Text <> "" And target.Column = streamingColumnIndex - 1 Then
Dim streamingNum As String, sublistNumber As String, prefixNum As String, rw As Long
rw = target.Row
'取得既有流水號與分單號
Numbers.reset_dictStreamingSublistNumPair
Numbers.streamingSublist_NumberBuilder target
'取得既有流水號與分單號後
'取得流水號儲存格
Dim cellStreaming As Range, streamingSublistNumArray
Set cellStreaming = Cells(target.Row, streamingColumnIndex + 2)
'streamingNum = Numbers.StreamingNumber
prefixNum = getPrefixNum(target)
streamingSublistNumArray = Numbers.LastStreamingSubstringNumArray(target, prefixNum)
streamingNum = streamingSublistNumArray(0)
sublistNumber = streamingSublistNumArray(1)
'流水號儲存格暫定在分單號同列前一欄(第5欄)輸入)
' If streamingNum <> "" Then '須在新的流水號輸入後才執行,因為須以流水號來查找可用的分單號
' '分單號儲存格
' sublistNumber = Numbers.sublistNumber(streamingNum)
' End If
'非程式在設定值而是手動輸入時才執行,因為程式在設定值時會觸發此事件
Application.EnableEvents = False '關閉事件程序
'自動填入流水號
ThisWorkbook.Unprotect
ActiveSheet.Unprotect
cellStreaming = streamingNum
'埴入分單號
Cells(target.Row, subListColumnIndex) = sublistNumber
'設定顯示當前分單號的版次
If sublistNumber = "0" Then
Cells(target.Row, subListColumnIndex + 2) = VBA.Replace(Cells(1, subListColumnIndex + 2), "X", "初")
Else
Cells(target.Row, subListColumnIndex + 2) = VBA.Replace(Cells(1, subListColumnIndex + 2), "X", sublistNumber)
End If
'填入編號記錄參考基 J 欄
' Cells(Target.Row, "J") = newNumber
'恢復保護
ActiveSheet.Protect
ThisWorkbook.Protect
'恢復事件程序
Application.EnableEvents = True
End If
'流水號欄位輸入後
Case 5
' ' Stop
' If target.Text <> "" And target.Column = streamingColumnIndex Then '須在新的流水號輸入後才執行,因為須以流水號來查找可用的分單號
' '分單號儲存格自動填入
' Numbers.reset_dictStreamingSublistNumPair
' Numbers.streamingSublist_NumberBuilder Cells(target.Row, target.Column - 1)
' sublistNumber = Numbers.sublistNumber(target.Text)
' Dim cellSublistNumber As Range
' Set cellSublistNumber = Cells(target.Row, subListColumnIndex)
' Application.EnableEvents = False
' ThisWorkbook.Unprotect
' ActiveSheet.Unprotect
' cellSublistNumber = sublistNumber
' '設定顯示當前分單號的版次
' If sublistNumber = "0" Then
' Cells(target.Row, subListColumnIndex + 2) = VBA.Replace(Cells(1, subListColumnIndex + 2), "X", "初")
' Else
' Cells(target.Row, subListColumnIndex + 2) = VBA.Replace(Cells(1, subListColumnIndex + 2), "X", sublistNumber)
' End If
' ThisWorkbook.Protect
' ActiveSheet.Protect
' Application.EnableEvents = True
'' Dim rw As Long
' rw = target.Row
' newNumber = Cells(rw, 1).Text & "-" & Cells(rw, 2).Text & "-" & Cells(rw, 3).Text & _
' "-" & Cells(rw, 4).Text & "-" & Cells(rw, 5).Text & "-" & Cells(rw, 6).Text
' Numbers.numberDictAppend newNumber
' '填入編號記錄參考基 J 欄
' Application.EnableEvents = False
' ThisWorkbook.Unprotect
' ActiveSheet.Unprotect
' Cells(target.Row, "J") = newNumber
' ThisWorkbook.Protect
' ActiveSheet.Protect
' Application.EnableEvents = True ' Numbers.Dispose
' End If
'分單號欄位輸入後
Case 6 '分單號輸入完後即加入新的編號記錄
If target.Text <> "" And target.Column = subListColumnIndex Then
End If
End Select
End Sub
Public Property Get newNumber() As String
newNumber = newNumberField
End Property
Public Property Let newNumber(ByVal vNewValue As String)
newNumberField = vNewValue
End Property
Function getPrefixNum(target As Range) As String
Dim prefixNum As String, rw As Long
rw = target.Row
prefixNum = Cells(rw, 1) & "-" & Cells(rw, 2) & "-" & Cells(rw, 3) & "-" & target.Text & "-"
getPrefixNum = prefixNum
End Function
Option Explicit
Rem 編號=品項號+流水號+分單號(分號)
Rem 用 Public 宣告是為了在活頁簿開啟時均能保特有效,隨時可供存取的緣故
' dict 存放已有編號:'鍵值與值均為編號
Public dict As New Scripting.Dictionary, dictStreamingSublistNumPair As New Scripting.Dictionary '存放已有流水號與分單號之資料(string 型別),鍵值Key為流水號,值value為分單號(Dictionary 型別)
' (未實作)prefix_streaming_sublistNum 存放現有編號的前綴(品項號)與流水號分單號映射的字典(key=編號前綴;value= dictStreamingSublistNumPair )
Public prefix_StreamingSublistNum As New Scripting.Dictionary
' prefix_streamingNum 存放流水號之前綴字元(即分單號之編號規則)
Dim prefix_streamingNum
'記下既有編號資料筆數
Public ExistedNumCount As Long
' 現有編號建置器
Sub numberDictBuilder()
Dim c As Range
Dim rng As Range
Set rng = ActiveSheet.UsedRange.Columns("J").Cells '編號參照基
'更新 ExistedNumCount 值,以供後來 Get NumberDict() 時比對
ExistedNumCount = rng.Count - 1 '去掉欄名
For Each c In rng
If c.Row > 1 Then '有欄名時
dict(c.Text) = c.Text '鍵值與值均為編號
' dict(c.Value) = c.Value
' ExistedNumCount = ExistedNumCount + 1
If c = "" Then Exit For
End If
Next c
End Sub
' 新增編號時更新現有編號器
Sub numberDictAppend(number As String) '(prefixNum As String, streamingNum As String, sublistNum As String)
Dim rng As Range, c As Range
Set rng = ActiveSheet.UsedRange.Columns("J").Cells '編號參照基
'更新 ExistedNumCount 值,以供後來 Get NumberDict() 時比對
ExistedNumCount = rng.Count - 1 '去掉欄名
dict(number) = number
'dict(prefixNum)(streamingNum) = sublistNum
'For Each c In rng
' If c.Row > 1 Then '有欄名時
' If Not dict.Exists(c.Text) Then dict(c.Text) = c.Text '鍵值與值均為編號
'
' If c = "" Then Exit For
' End If
'Next c
End Sub
Rem 取得已有編號記錄,回傳一個字典型別的值
Public Property Get NumberDict() As Dictionary
If dict.Count = 0 Then
numberDictBuilder
Else
Dim countNumRecords As Long ', c As Range
countNumRecords = ActiveSheet.UsedRange.Columns("J").Cells.Count
' For Each c In ActiveSheet.UsedRange.Columns("j").Cells '暫定 J 欄是所有已有編號的記錄欄位
' If c.Text <> "" Then countNumRecords = countNumRecords + 1
' Next c
'如果編號資料有增加的話,則自動更新編號記錄(字典Dictionary型別)
Rem 這行及上一行還待定,要確定資料基的存取處後再做較好!!!!
If ExistedNumCount < countNumRecords - 1 Then '-1 含欄名 ; ' 現在未完成但有輸入的列也會算進去+欄名,故要-2
'Numbers.Dispose
'numberDictBuilder
' numberDictAppend
Else
ExistedNumCount = countNumRecords - 1 '去掉欄名;記下現在編號記錄數,以供下次比對,是否須更新(追加或重建)編號記錄
End If
End If
Set NumberDict = dict
End Property
Rem 已存在流水號與分單號清單建立器:取得既有流水號與分單號 例 'A-BOX-SIY-A035-001-A
Sub streamingSublist_NumberBuilder(target As Range)
Dim dict As Dictionary, key, orderNumberPrefix As String, orderNumberPrefixLen As Byte, rw As Long, numSetArr() As String, sublistNumbers As New Scripting.Dictionary '分單號的字典,作為 dictStreamingSublistNumPair 的值(value)
rw = target.Row
Set dict = Numbers.NumberDict
'取得現在輸入的流水號前的品項號(暫定為1~4欄。目前設定在第4欄輸入品項號最後一部分)
orderNumberPrefix = Cells(rw, 1).Text & "-" & Cells(rw, 2).Text & "-" & Cells(rw, 3).Text & "-" & target.Text & "-"
'取得現在在輸入的流水號前的品項號的長度以作為判斷流水號的位置起始處
orderNumberPrefixLen = VBA.Len(orderNumberPrefix)
For Each key In dict
'逐一與已有之編號的流水號作比對
If Left(key, orderNumberPrefixLen) = orderNumberPrefix Then '如果既有的編號之品項號與現在輸入的相符合,
'就解析出它的流水號和分單號(分號)來,以供記錄。以「-」分割二號開來
numSetArr = VBA.Split(Mid(key, orderNumberPrefixLen + 1), "-") '取得的值是一個一維陣列,第一個元素即已有的流水號,第二個元素即分單號
'取得既有流水號與分單號
If dictStreamingSublistNumPair.Exists(numSetArr(0)) Then '如果流水號存在的話
Set sublistNumbers = dictStreamingSublistNumPair(numSetArr(0))
sublistNumbers(numSetArr(1)) = numSetArr(1) '取得既有的分單號,存入sublistNumbers 字典(Dictionary 型別)中
Set dictStreamingSublistNumPair(numSetArr(0)) = sublistNumbers '儲存既有的分單號
'如果流水號並不曾存在的話
Else
If sublistNumbers.Count > 0 Then Set sublistNumbers = Nothing '清空原有記錄(如果有的話)以備用 ps. 用 removeall 方法可能會影響原來存放的資料,Nothing 則不會
sublistNumbers.Add numSetArr(1), numSetArr(1)
Set dictStreamingSublistNumPair(numSetArr(0)) = sublistNumbers
End If
End If
Next key
End Sub
Sub reset_dictStreamingSublistNumPair()
dictStreamingSublistNumPair.RemoveAll
End Sub
Sub Dispose()
dict.RemoveAll
Set dict = Nothing
dictStreamingSublistNumPair.RemoveAll
Set dictStreamingSublistNumPair = Nothing
ExistedNumCount = 0
End Sub
Rem 001~999、A01……
'取得新的流水號
Public Property Get StreamingNumber() As String
Dim i As Byte, strNumber
Dim pre, pr, startNum As Byte
pre = GetPrefix_streamingNum(True)
For Each pr In pre
'如果開頭是數字,要連號;即要有00(100、200……),不能從01開始(101、201……
If VBA.IsNumeric(pr) Then
If VBA.CByte(pr) > 0 Then
startNum = 0
Else '如果是數字且開頭是「0」,才從1 開始(沒有000 號,須從001起編流水號)
startNum = 1
End If
Else '如果字母,則定從01開始(A01、B01……)
startNum = 1
End If
For i = startNum To 99
strNumber = pr + Format(i, "00")
If Not dictStreamingSublistNumPair.Exists(strNumber) Then
dictStreamingSublistNumPair.Add strNumber, New Scripting.Dictionary
StreamingNumber = strNumber
Exit Property
End If
Next i
Next pr
'…… A01 、 B01 ……
End Property
Property Get LastStreamingSubstringNumArray(target As Range, prefixNum As String) As Variant
'If dict.Count = 0 Then
' numberDictBuilder
'' streamingSublist_NumberBuilder
'End If
Dim arr(1), dictSublist As Scripting.Dictionary
streamingSublist_NumberBuilder target
arr(0) = dictStreamingSublistNumPair.Keys(dictStreamingSublistNumPair.Count - 1)
Set dictSublist = dictStreamingSublistNumPair(arr(0))
arr(1) = dictSublist.Keys(dictSublist.Count - 1)
LastStreamingSubstringNumArray = arr
End Property
'取得新的分單號。傳入剛輸入的新的流水號引數 StreamingNum
Public Function sublistNumber(streamingNum As String) As String
'例: A-BOX-SIY-A035-001-A
Dim strSubNumber
Dim prefixs, ePrefixs
Dim dict As New Scripting.Dictionary, eCln, flagExisted As Boolean
'取得流水號之前綴字元(即分單號之編號規則)
If TypeName(dictStreamingSublistNumPair(streamingNum)) = "Empty" Then
dict.Add "0", "0"
strSubNumber = "0" '若還沒有就傳回分單號起始編號 0
Set dictStreamingSublistNumPair(streamingNum) = dict '將此新的分單號加入到 dictStreamingSublistNumPair 中
sublistNumber = strSubNumber
Exit Function
ElseIf dictStreamingSublistNumPair(streamingNum).Count = 0 Then
dict.Add "0", "0"
strSubNumber = "0" '若還沒有就傳回分單號起始編號 0
Set dictStreamingSublistNumPair(streamingNum) = dict '將此新的分單號加入到 dictStreamingSublistNumPair 中
sublistNumber = strSubNumber
Exit Function
Else '若已有舊的分單號,則找出可以用的作為新分單號
prefixs = GetPrefix_streamingNum(False)
For Each ePrefixs In prefixs
'取得新的分單號
strSubNumber = ePrefixs
Set dict = dictStreamingSublistNumPair(streamingNum) '取出既有分單號的字典
If dict.Exists(strSubNumber) Then flagExisted = True
If Not flagExisted Then
'如果既有的分單號還沒有這個值,就回傳它
dictStreamingSublistNumPair(streamingNum).Add strSubNumber, strSubNumber
sublistNumber = strSubNumber
Exit Function
End If
flagExisted = False
Next ePrefixs
End If
End Function
'取得流水號之前綴字元(即分單號之編號規則)
Public Property Get GetPrefix_streamingNum(streaming As Boolean) As Variant
If streaming Then
prefix_streamingNum = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
Else
prefix_streamingNum = Array("0", "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
End If
GetPrefix_streamingNum = prefix_streamingNum
End Property
孫守真任真甫大大,首先必須感謝您這麼有耐心的一再修改程式來幫助我,真的非常感謝!
為了讓您能夠了解我當初製作這個表時的邏輯想法,再此回覆末尾附上我的範例檔案供您參考。
其實您上述提到的部分已與我當初用公式製作時的想法很接近,要使用者可以輸入流水號,但不能輸入分單號。在使用者輸入完流水號後,才由程式自動賦予不重號的分單號,是這樣嗎???這樣要怎麼確定使用者真的輸入完流水號,而不再改變了呢?還是希望使用者可以輸入改變
我之前一直沒有提到的地方,是關於編號自動生成的部分,再說這部分之前,先解答您上述的疑問"要使用者可以輸入流水號,但不能輸入分單號。在使用者輸入完流水號後,才由程式自動賦予不重號的分單號,是這樣嗎???這樣要怎麼確定使用者真的輸入完流水號,而不再改變了呢?還是希望使用者可以輸入改變"
我自知自己的表達能力欠缺,故詳細的功能您可以透過底下的範例檔案來測試,若還有疑問,是否能與您再約個時間透過線上會議之類的方式來做說明,以上,謝謝您。
Bobo0509菩薩慈悲:
1.【真正決定流水號的是手動輸入的欄位(圖一的C9欄位),程式判別那欄只作當前最新編號的顯示(圖一的G9欄位)】
所以C9是顯示到底是可以編的新號(容許使用者輸入的新號,即尚未編入編號者),還是最後編的舊號?(請明示!)
2.同樣的【在圖一所看到的紅字非白底的是程式判別後顯示當前最新分單號(圖一的M9欄位),】所以,M9也是可以用的新號,而不是舊有最後的一號嗎?
★我看您圖一與範例檔分明G9與M9是舊有最後(或最近)的流水號與分單號嘛!為什麼說「顯示當前最新分單號」?您所謂的「最新」是舊有的最近(新)嗎?不是可編的新號吧。請以上2個疑問一定要先搞定,我才會繼續下去,免得又白做了。(我有空妳又還沒回覆的話,會用舊有的最後一筆編號來算,而不是顯示新的可輸入的編號})感恩感恩 南無阿彌陀佛
3.為什麼分單號還要分兩個欄位?明明一個欄位便可敷用了……這裡末學還是感到很困惑也奇怪!您所謂的兩個欄位,第2個是N9(與O9合併?)嗎。但您回應說明裡又說和C11有關(是C11D11到G11合併或跨欄的欄位吧?)這個C11為什麼不用「訂單總表」工作表中的訂單編號欄位值或訂單表顯示內容欄位半形空格前的值回傳就好?還有「訂單總表」為什麼訂單編號欄位和訂單表顯示內容欄位所顯示的值又不同了呢?前者是舊號,後者是取得使用輸入後新編而加入記錄的編號嗎?請菩薩您務必先回答以上問題。感恩感恩
●有實際的範例檔就好操作多了,不但能具體掌握您應用程式的流程邏輯,我也可以直接把程式碼掛上去測試。您早該給我了。我先試試吧。但可能得請菩薩您稍安勿躁,畢竟前面徒勞往返白費了許多心思,我還有別的要忙,餘暇才能分神理會。想能見諒。感恩感恩 南無阿彌陀佛
4.我看到「CommandButton1_Click」,這個按鍵就是使用者確定輸入完時的動作嗎?也就是按下就不會再更改編號記錄中的記錄了。是嗎?而不是在C9更動/輸入後。是這樣嗎?這很重要,就是我前面提到的程式碼更新編號記錄的時機點。
> 今天晚上7點半可以的,我再透過您說的方式聯繫您,謝謝
抱歉,可能是我的用詞產生誤會...在這裡先向您道歉
1.【真正決定流水號的是手動輸入的欄位(圖一的C9欄位),程式判別那欄只作當前最新編號的顯示(圖一的G9欄位)】
所以C9是顯示到底是可以編的新號(容許使用者輸入的新號,即尚未編入編號者),還是最後編的舊號?(請明示!)
在我的想法裡,"顯示最新編號"是指總表裡最後編到的號碼,例如{在不考慮手動輸入流水號的情況下}總表裡訂單編號的流水號最後一號為056,那新的流水號正常就會是057,在圖一的G9欄位會顯示"056",在圖一C11到G11合併的欄位裡,流水號會顯示057(讓使用者確認,避免重號)
2.同樣的【在圖一所看到的紅字非白底的是程式判別後顯示當前最新分單號(圖一的M9欄位),】所以,M9也是可以用的新號,而不是舊有最後的一號嗎?
★我看您圖一與範例檔分明G9與M9是舊有最後(或最近)的流水號與分單號嘛!為什麼說「顯示當前最新分單號」?您所謂的「最新」是舊有的最近(新)嗎?不是可編的新號吧。
在圖一M9欄位裡,同樣是只顯示總表裡最後編到的號碼(為讓使用者確認)
真正新編的編號會出現在圖一C11到G11合併的欄位裡
3.為什麼分單號還要分兩個欄位?明明一個欄位便可敷用了……這裡末學還是感到很困惑也奇怪!您所謂的兩個欄位,第2個是N9(與O9合併?)嗎。但您回應說明裡又說和C11有關(是C11D11到G11合併或跨欄的欄位吧?)這個C11為什麼不用「訂單總表」工作表中的訂單編號欄位值或訂單表顯示內容欄位半形空格前的值回傳就好?
3.1.這範例裡的表改過很多次,自己本身對excel並不精通,加上測試時有出現重號或是不知道總表已編到的最後一號為何?才會使用兩個欄位,
從圖一的M9欄位顯示總表最後編到的分單號,在圖一的C11到G11合併欄位裡顯示新編的訂單編號(未增列到總表裡的編號,待新增的新編號)
還有「訂單總表」為什麼訂單編號欄位和訂單表顯示內容欄位所顯示的值又不同了呢?前者是舊號,後者是取得使用輸入後新編而加入記錄的編號嗎?
3.2.在圖一的C11到G11合併欄位裡顯示新編的訂單編號(未增列到總表裡的編號,待新增的新編號)
而總表裡的是已經建立過的編號(舊號),類似資料庫的記錄數據
4.我看到「CommandButton1_Click」,這個按鍵就是使用者確定輸入完時的動作嗎?也就是按下就不會再更改編號記錄中的記錄了。是嗎?而不是在C9更動/輸入後。是這樣嗎?這很重要,就是我前面提到的程式碼更新編號記錄的時機點。
4.1.沒有按下CommandButton1_Click按鍵確認建立前,手動輸入的C9欄位,只會影響,"M9欄位"與"C11到G11合併的欄位"所顯示的號碼,不會影響到總表裡的內容;
4.2.在登記表輸入完待新編的內容後,按下CommandButton1_Click按鍵,確定新增後,才會將登記表中的內容增加到總表裡,變成一筆新的數據。
只要沒有按下CommandButton1_Click按鍵,登記表裡的內容再怎麼變動,都不會影響到總表。
我怕我又表達得不清楚,只能麻煩您從範例檔裡測試看看,實在抱歉花費您這麼多的精力和時間,也很感謝您的回覆。
Bobo0509那什麼時候顯示出紅字的部分(G9、M9、N9)呢?是確認H5、M5、C7、H7(即編號前綴)都輸入了有資料之後嗎?還是有上述哪個儲存格輸入完後(比如H7),才顯示舊有記錄的編號現況?因為必須取得上述4個前綴參考的儲存格值才能去判斷已存在的流水號的編號情況。
至於M9的顯示,要與C9 同步同時,還是在流水號有資料輸入後才顯示呢?又是否要依流水號的輸入情況,再依其輸入之值判斷分單號的顯示值呢 請菩薩您明示
確定上述情況,我就大致懂了。感恩感恩 南無阿彌陀佛
1.那什麼時候顯示出紅字的部分(G9、M9、N9)呢?是確認H5、M5、C7、H7(即編號前綴)都輸入了有資料之後嗎?還是有上述哪個儲存格輸入完後(比如H7),才顯示舊有記錄的編號現況?
範例檔案裡,我用公式判別時,是以H5、C7當作基準(H5、C7有資料輸入),圖三的D1欄位才會將圖一的H5、M5、C7、H7、C9組合起來作為判別字串,再從這組字串去作延伸和公式判別計算
可以先將圖一的C9當作基準,C9無資料輸入時,G9、M9、N9、C11不顯示,設定後測試看看有沒有問題,我自己列公式時也是反覆測試,所以無法完全保證,以C9當基準就是最好的方式
2.至於M9的顯示,要與C9 同步同時,還是在流水號有資料輸入後才顯示呢?又是否要依流水號的輸入情況,再依其輸入之值判斷分單號的顯示值呢?
目前我在範例檔案裡的公式是同步同時,只要C9輸入值變更,M9與C11就要同步一起變化,就是依流水號輸入的情況,再依其輸入之值判斷分單號的顯示值
因為自己對於VBA代碼撰寫與應用上不甚了解,也未曾學習過,所以考慮的較欠缺,只能依照自己目前的公式方法作依據來回答您,實在抱歉
菩薩慈悲:終於大抵搞定了。感恩感恩 讚歎讚歎 南無阿彌陀佛
最新程式碼如下:
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "Sheet2"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Rem 佛弟子文獻學者孫守真任真甫謹製 20230321~24
Const storeCellName As String = "H5" '店名
Const unitCellName As String = "M5" '單位
Const itemTypeCellName As String = "C7" '品項類別
Const itemIDCellName As String = "H7" '品項代號
Const streamingInputCellName As String = "C9" '流水號儲存格
Const streamingShowCellName As String = "G9" '流水號儲存格
'當前無資料可新建",IF(編號產生器!$C$7<>"","當前文件編號已編到"))))
Const streamingShowMsgNotExisted As String = "當前無資料可新建" ''流水號不存在訊息
Const streamingShowMsgExisted As String = "當前文件編號已編到" ''流水號存在訊息
Const streamingShowMsgCellName As String = "D9" '流水號存在與否訊息儲存格
Const subListCellName As String = "M9" '分單號儲存格
Const subListVersionCellName As String = "N9" '分單號當前版本儲存格
Const subListVersionMessage As String = "當前為X版次" 'N9 分單號當前版本儲存格要顯示的文字訊息
Const numberShowCellName As String = "C11" '訂單編號 to show
Const numberColumn As String = "C" 'Sheets("訂單總表")欄位值
'記下新的編號
Dim newNumberField As String
Private Sub inputNum(Target As Range)
Dim streamingNum As String
If Target = "" Then
Exit Sub
Else
streamingNum = Format(Target, "000")
End If
Dim sublistNumber As String, prefixNum As String
'取得編號前綴
prefixNum = getPrefixNum
'取得含有前綴已編過的流水號與分單號
Numbers.reset_dictStreamingSublistNumPair
Numbers.streamingSublist_NumberBuilder ' Target
If Numbers.dictStreamingSublistNumPair.Exists(streamingNum) Then '如果已有流水號
'取得新的分單號
sublistNumber = Numbers.sublistNumber(streamingNum)
Else '如果尚無流水號
sublistNumber = "0"
End If
'在訂單編號(C11)儲存格顯示
newNumber = prefixNum + streamingNum & "-" & sublistNumber
'非程式在設定值而是手動輸入時才執行,因為程式在設定值時會觸發此事件
Application.EnableEvents = False '關閉事件程序
Range(numberShowCellName) = newNumber
'埴入分單號以顯示在M9
Range(subListCellName) = sublistNumber
'設定顯示當前分單號的版次N9
If sublistNumber = "0" Then
Range(subListVersionCellName) = VBA.Replace(subListVersionMessage, "X", "初")
Else
Range(subListVersionCellName) = VBA.Replace(subListVersionMessage, "X", sublistNumber)
End If
'恢復事件程序
Application.EnableEvents = True
End Sub
Private Sub showNum()
Dim streamingNum As String, sublistNumber As String, prefixNum As String
'取得既有流水號與分單號
Numbers.reset_dictStreamingSublistNumPair
Numbers.streamingSublist_NumberBuilder ' Target
'取得既有流水號與分單號後
'取得流水號儲存格
Dim streamingSublistNumArray
'streamingNum = Numbers.StreamingNumber
prefixNum = getPrefixNum()
'取得同編號前綴最後加入的流水號與分單號,以分別顯示在 G9、M9(及N9)
streamingSublistNumArray = Numbers.LastStreamingSubstringNumArray(prefixNum) '(Target, prefixNum)
streamingNum = streamingSublistNumArray(0) '流水號
sublistNumber = streamingSublistNumArray(1) '分單號
'非程式在設定值而是手動輸入時才執行,因為程式在設定值時會觸發此事件
Application.EnableEvents = False '關閉事件程序
' ThisWorkbook.Unprotect
' ActiveSheet.Unprotect
'自動填入流水號以供顯示在G9
Range(streamingShowCellName) = streamingNum
'在D9顯示相關訊息
If streamingSublistNumArray(2) Then
Range(streamingShowMsgCellName) = streamingShowMsgExisted
Else
Range(streamingShowMsgCellName) = streamingShowMsgNotExisted
End If
'埴入分單號以顯示在M9
Range(subListCellName) = sublistNumber
'設定顯示當前分單號的版次N9
If sublistNumber = "0" Then
Range(subListVersionCellName) = VBA.Replace(subListVersionMessage, "X", "初")
Else
Range(subListVersionCellName) = VBA.Replace(subListVersionMessage, "X", sublistNumber)
End If
' '恢復保護
' ActiveSheet.Protect
' ThisWorkbook.Protect
'恢復事件程序
Application.EnableEvents = True
End Sub
Public Property Get newNumber() As String
newNumber = newNumberField
End Property
Public Property Let newNumber(ByVal NewValue As String)
newNumberField = NewValue
End Property
Public Function getPrefixNum() As String
Dim prefixNum As String, rw As Long
prefixNum = Range(storeCellName) & "-" & Range(unitCellName) & "-" & Range(itemTypeCellName) & "-" & Range(itemIDCellName) & "-"
getPrefixNum = prefixNum
End Function
Rem 以下為原有程式碼,非鄙人之部分以'……略去
Private Sub CommandButton1_Click()
Const fontSize As Single = 16
'……
Numbers.numberDictAppend Range(numberShowCellName), getPrefixNum
'……
CommandButton1.Font.Size = fontSize
End Sub
'……Rem end 以上為原有程式碼
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo eH
'使用者輸入流水號(儲存格C9)後
' If Target.Row = Range(streamingInputCellName).Row And Target.Column = Range(streamingInputCellName).Column Then
If Not Intersect(Target, Range(streamingInputCellName)) Is Nothing Then
inputNum Target
'品項代號輸入完後
ElseIf Not Intersect(Target, Range(itemIDCellName)) Is Nothing Or Not Intersect(Target, Range(itemTypeCellName)) Is _
Nothing Or Not Intersect(Target, Range(unitCellName)) Is Nothing Or Not Intersect(Target, Range(storeCellName)) Is Nothing Then
If Range(storeCellName) <> "" And Range(unitCellName) <> "" And Range(itemTypeCellName) <> "" And Range(itemIDCellName) <> "" Then
showNum
End If
End If
Exit Sub
eH:
Select Case Err.number
Case 50290 ''Intersect' 方法 ('_Global' 物件) 失敗
Case Else
MsgBox Err.number + Err.Description
End Select
End Sub
Attribute VB_Name = "Numbers"
Rem 佛弟子文獻學者孫守真任真甫謹製 20230321~24
Option Explicit
Rem 編號=品項號(編號前綴)+流水號+分單號(分號)
Rem 用 Public 宣告是為了在活頁簿開啟時均能保特有效,隨時可供存取的緣故
' dict 存放已有編號:'鍵值為編號,值為編號前綴
Public dict As New Scripting.Dictionary, dictStreamingSublistNumPair As New Scripting.Dictionary '存放已有流水號與分單號之資料(string 型別),鍵值Key為流水號,值value為分單號(Dictionary 型別)
' (未實作)prefix_streaming_sublistNum 存放現有編號的前綴(品項號)與流水號分單號映射的字典(key=編號前綴;value= dictStreamingSublistNumPair )
Public prefix_StreamingSublistNum As New Scripting.Dictionary
' prefix_streamingNum 存放流水號之前綴字元(即分單號之編號規則)
Dim prefix_streamingNum
' Sheets("訂單總表")的欄名列數
Const columnRowsCount As Byte = 2
'取得既有編號資料筆數
Public Property Get ExistedNumCount() As Long
'更新 ExistedNumCount 值,以供後來 Get NumberDict() 時比對
ExistedNumCount = ExistedNumColumnRange.Count - columnRowsCount '去掉欄名
End Property
Public Property Get ExistedNumColumnRange() As Range
'取得所有編號記錄作參照基
Set ExistedNumColumnRange = Sheets("訂單總表").UsedRange.Columns("C").Cells
End Property
' 現有編號建置器
Sub numberDictBuilder()
Dim c As Range, xc As String
Dim rng As Range
'取得所有編號記錄作參照基
Set rng = ExistedNumColumnRange
''更新 ExistedNumCount 值,以供後來 Get NumberDict() 時比對
'existedNumCount = rng.Count - columnRowsCount '去掉欄名
For Each c In rng
If c.Row > columnRowsCount Then '除了欄名列,即編號記錄列
Rem !!!注意,編號記錄不能有空值!!!
If c = "" Then Exit For
xc = c.Value 'c.Text 用 Text屬性慢很多,用value 就瞬間好了 Bing大菩薩 20230324
dict(xc) = VBA.Left(xc, VBA.InStrRev(VBA.Left(xc, VBA.InStrRev(xc, "-") - 1), "-")) '鍵值為編號,值為編號前綴
End If
Next c
End Sub
' 新增編號時更新現有編號器
Sub numberDictAppend(number As String, prefixNum As String) '(prefixNum As String, streamingNum As String, sublistNum As String)
dict(number) = prefixNum ' number
End Sub
Rem 取得已有編號記錄,回傳一個字典型別的值
Public Property Get NumberDict() As Dictionary
If dict.Count = 0 Then numberDictBuilder
Set NumberDict = dict
End Property
Rem 已存在流水號與分單號清單建立器:取得既有流水號與分單號 例 'A-BOX-SIY-A035-001-A
Sub streamingSublist_NumberBuilder() '(Target As Range)
Dim dict As Dictionary, key, orderNumberPrefix As String, orderNumberPrefixLen As Byte, numSetArr() As String, sublistNumbers As New Scripting.Dictionary '分單號的字典,作為 dictStreamingSublistNumPair 的值(value)
Set dict = Numbers.NumberDict
'取得現在輸入的流水號前的品項號
orderNumberPrefix = Sheet2.getPrefixNum
'取得現在在輸入的流水號前的品項號的長度,以作為判斷流水號的位置起始處
orderNumberPrefixLen = VBA.Len(orderNumberPrefix)
For Each key In dict
'逐一與已有之編號的流水號作比對
'If Left(key, orderNumberPrefixLen) = orderNumberPrefix Then '如果既有的編號之品項號與現在輸入的相符合,
If dict(key) = orderNumberPrefix Then '如果既有的編號之品項號與現在輸入的相符合,
'就解析出它的流水號和分單號(分號)來,以供記錄。以「-」分割二號開來
numSetArr = VBA.Split(Mid(key, orderNumberPrefixLen + 1), "-") '取得的值是一個一維陣列,第一個元素即已有的流水號,第二個元素即分單號
'取得既有流水號與分單號
If dictStreamingSublistNumPair.Exists(numSetArr(0)) Then '如果流水號存在的話
Set sublistNumbers = dictStreamingSublistNumPair(numSetArr(0))
sublistNumbers(numSetArr(1)) = numSetArr(1) '取得既有的分單號,存入sublistNumbers 字典(Dictionary 型別)中
Set dictStreamingSublistNumPair(numSetArr(0)) = sublistNumbers '儲存既有的分單號
'如果流水號並不曾存在的話
Else
If sublistNumbers.Count > 0 Then Set sublistNumbers = Nothing '清空原有記錄(如果有的話)以備用 ps. 用 removeall 方法可能會影響原來存放的資料,Nothing 則不會
sublistNumbers.Add numSetArr(1), numSetArr(1)
Set dictStreamingSublistNumPair(numSetArr(0)) = sublistNumbers
End If
End If
Next key
End Sub
Sub reset_dictStreamingSublistNumPair()
dictStreamingSublistNumPair.RemoveAll
End Sub
Sub Dispose()
dict.RemoveAll
Set dict = Nothing
dictStreamingSublistNumPair.RemoveAll
Set dictStreamingSublistNumPair = Nothing
End Sub
Rem 001~999、A01……
'取得新的流水號
Public Property Get StreamingNumber() As String
Dim i As Byte, strNumber
Dim pre, pr, startNum As Byte
pre = GetPrefix_streamingNum(True)
For Each pr In pre
'如果開頭是數字,要連號;即要有00(100、200……),不能從01開始(101、201……
If VBA.IsNumeric(pr) Then
If VBA.CByte(pr) > 0 Then
startNum = 0
Else '如果是數字且開頭是「0」,才從1 開始(沒有000 號,須從001起編流水號)
startNum = 1
End If
Else '如果字母,則定從01開始(A01、B01……)
startNum = 1
End If
For i = startNum To 99
strNumber = pr + Format(i, "00")
If Not dictStreamingSublistNumPair.Exists(strNumber) Then
dictStreamingSublistNumPair.Add strNumber, New Scripting.Dictionary
StreamingNumber = strNumber
Exit Property
End If
Next i
Next pr
'…… A01 、 B01 ……
End Property
'取得最後加入的流水號與分單號,回傳一個陣列:元素1=流水號;元素2=分單號;元素3=是否已存在(存在=true,不存在而傳回預設起始值=false)
Property Get LastStreamingSubstringNumArray(prefixNum As String) As Variant '(Target As Range, prefixNum As String) As Variant
'If dict.Count = 0 Then
' numberDictBuilder
'' streamingSublist_NumberBuilder
'End If
Dim arr(2), dictSublist As Scripting.Dictionary
' streamingSublist_NumberBuilder 'Target
'如果還沒對應前綴的流水號與分單號
If dictStreamingSublistNumPair.Count = 0 Then
'不存在而傳回預設起始值
arr(0) = "001": arr(1) = "0"
arr(2) = False
Else
arr(0) = dictStreamingSublistNumPair.Keys(dictStreamingSublistNumPair.Count - 1) '流水號
Set dictSublist = dictStreamingSublistNumPair(arr(0))
arr(1) = dictSublist.Keys(dictSublist.Count - 1) '分單號
arr(2) = True
End If
LastStreamingSubstringNumArray = arr
End Property
'取得新的分單號。傳入剛輸入的新的流水號引數 StreamingNum
Public Function sublistNumber(streamingNum As String) As String
'例: A-BOX-SIY-A035-001-A
Dim strSubNumber
Dim prefixs, ePrefixs
Dim dict As New Scripting.Dictionary, eCln, flagExisted As Boolean
'取得流水號之前綴字元(即分單號之編號規則)
If TypeName(dictStreamingSublistNumPair(streamingNum)) = "Empty" Then
dict.Add "0", "0"
strSubNumber = "0" '若還沒有就傳回分單號起始編號 0
Set dictStreamingSublistNumPair(streamingNum) = dict '將此新的分單號加入到 dictStreamingSublistNumPair 中
sublistNumber = strSubNumber
Exit Function
ElseIf dictStreamingSublistNumPair(streamingNum).Count = 0 Then
dict.Add "0", "0"
strSubNumber = "0" '若還沒有就傳回分單號起始編號 0
Set dictStreamingSublistNumPair(streamingNum) = dict '將此新的分單號加入到 dictStreamingSublistNumPair 中
sublistNumber = strSubNumber
Exit Function
Else '若已有舊的分單號,則找出可以用的作為新分單號
prefixs = GetPrefix_streamingNum(False)
For Each ePrefixs In prefixs
'取得新的分單號
strSubNumber = ePrefixs
Set dict = dictStreamingSublistNumPair(streamingNum) '取出既有分單號的字典
If dict.Exists(strSubNumber) Then flagExisted = True
If Not flagExisted Then
'如果既有的分單號還沒有這個值,就回傳它
' dictStreamingSublistNumPair(streamingNum).Add strSubNumber, strSubNumber
sublistNumber = strSubNumber
Exit Function
End If
flagExisted = False
Next ePrefixs
End If
End Function
'取得流水號之前綴字元(即分單號之編號規則)
Public Property Get GetPrefix_streamingNum(streaming As Boolean) As Variant
If streaming Then
prefix_streamingNum = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
Else
prefix_streamingNum = Array("0", "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
End If
GetPrefix_streamingNum = prefix_streamingNum
End Property
孫守真任真甫孫博士您好,您所提供的代碼我早上有作一些測試,但有一些小小的問題想再請教您
1.當登記表的流水號與分單號在總表裡沒有記錄時,能不能在紅字顯示欄位裡顯示為"N/A"(表示無資料的意思),我自己有嘗試改動,目前已改成能顯示為"N/A"了[您原來的代碼顯示會是初始值"流水號為001"和"分單號為0"]
但分單號在N9欄位裡會顯示為"當前為N/A版次",這個我實在不知道要從哪裡修改或新增IF判別,怕改到不該改的地方(如下圖所示)
我希望當分單號在總表無資料記錄時,N9欄位會顯示"無資料可新建"的字樣,以下是我有修改的代碼:
'模組 Numbers
'取得最後加入的流水號與分單號,回傳一個陣列:元素1=流水號;元素2=分單號;元素3=是否已存在(存在=true,不存在而傳回預設起始值=false)
Property Get LastStreamingSubstringNumArray(prefixNum As String) As Variant '(Target As Range, prefixNum As String) As Variant
'If dict.Count = 0 Then
' numberDictBuilder
'' streamingSublist_NumberBuilder
'End If
Dim arr(2), dictSublist As Scripting.Dictionary
' streamingSublist_NumberBuilder 'Target
'如果還沒對應前綴的流水號與分單號
If dictStreamingSublistNumPair.Count = 0 Then
'不存在而傳回預設起始值
arr(0) = "N/A": arr(1) = "N/A"
arr(2) = False
Else
arr(0) = dictStreamingSublistNumPair.Keys(dictStreamingSublistNumPair.Count - 1) '流水號
Set dictSublist = dictStreamingSublistNumPair(arr(0))
arr(1) = dictSublist.Keys(dictSublist.Count - 1) '分單號
arr(2) = True
End If
LastStreamingSubstringNumArray = arr
End Property
Private Sub inputNum(Target As Range)
Dim streamingNum As String
If Target = "" Then
Exit Sub
Else
streamingNum = Format(Target, "000")
End If
Dim sublistNumber As String, prefixNum As String
'取得編號前綴
prefixNum = getPrefixNum
'取得含有前綴已編過的流水號與分單號
Numbers.reset_dictStreamingSublistNumPair
Numbers.streamingSublist_NumberBuilder ' Target
If Numbers.dictStreamingSublistNumPair.Exists(streamingNum) Then '如果已有流水號
'取得新的分單號
sublistNumber = Numbers.sublistNumber(streamingNum)
Else '如果尚無流水號
sublistNumber = "N/A"
End If
If sublistNumber = "N/A" Then
'在訂單編號(C11)儲存格顯示
newNumber = prefixNum + streamingNum & "-" & "0"
Else
newNumber = prefixNum + streamingNum & "-" & sublistNumber
End If
'非程式在設定值而是手動輸入時才執行,因為程式在設定值時會觸發此事件
Application.EnableEvents = False '關閉事件程序
Range(numberShowCellName) = newNumber
'埴入分單號以顯示在M9
Range(subListCellName) = sublistNumber
'設定顯示當前分單號的版次N9
If sublistNumber = "0" Then
Range(subListVersionCellName) = VBA.Replace(subListVersionMessage, "X", "初")
Else
Range(subListVersionCellName) = VBA.Replace(subListVersionMessage, "X", sublistNumber)
End If
'恢復事件程序
Application.EnableEvents = True
End Sub
2.您所提供的檔案我測試時,發現了一個小小問題(應該是基準欄位判別的選定或是程式計算時的判別依據),當我在H7欄位輸入品項代號後,流水號D9、G9欄位與分單號M9、N9欄位顯示是正確的,但是當我接著在C9欄位輸入流水號後,分單號M9、N9欄位顯示出的是待新編的號碼,例如總表裡分單號最後編到的號碼為B,在我輸入品項代號後,分單號M9、N9欄位顯示的都是B,當我接著在C9欄位輸入相同流水號後,分單號M9、N9欄位卻會顯示為C,因為是顯示當前總表最後編到的號碼,分單號M9、N9欄位應該還要顯示B而不是C(分單號C版次還未新增到總表裡,總表不會有C的記錄)
當使用者輸入相同或不同流水號時,M9、N9欄位會重新讀取資料作判別;但M9、N9欄位只會顯示總表裡已編到的最後號碼,不會跳為待新編的號碼
詳細情況再麻煩您請看我錄製的視頻影片(避免因文字敘述表達不良而產生誤會)
3.我有自己嘗試使用VBA的"自訂表單"功能,繪製表單,不過在流水號與分單號的顯示與判別還是不清楚如何編寫代碼,其他功能的VBA代碼我已放在表單裡,若您有時間再請您能幫我看看哪裡要再修改,這是我自己額外做的部分,想多學習一些但自身能力不足,故想請教您,但希望您不要因此而有所壓力,不想因為我個人的事情而耽誤您影響您的事情,我可以自己繼續摸索思考,再次感謝您對我的幫助,讓我獲益良多也學到很多(雖然代碼有90%都看不太明白),看到您如此費心費力只為幫助我,真的非常非常感謝您。
Bobo0509菩薩慈悲:看太多文字我都發麻了,也未必就能掌握您確切的需要,且這裡內容太多,頁面切換又太不方便,不如會議討論交流來得簡潔有效,想昨晚對談之效益已很顯著明確。您既然不急,我時間又較彈性,可否請您有任何問題和我約個時間如昨晚一樣快刀斬麻,才能大快人心。這些多文字部分,我就先隨自己方便,加減處理了。感恩感恩 南無阿彌陀佛
孫守真任真甫孫博士您好,我會在簡訊裡與您約時間以會議方式交流,再次感謝您的幫助與回復,謝謝您。
如果想改用VBA查詢是否重號
(大義上)創立已有的流水號sheet,
然後直接跳到底端看最後的流水號是多少,
新增資料=>流水號+1,回傳流水號於sheet
舉例來說,A總店,box盒,DNS日用品,流水號001
你應該先創建一欄a-box-dns-
這欄底下會有a-box-dns-001、a-box-dns-002、a-box-dns-003...。
直接跳到該欄底下找到最後的編號,+1就是新的流水號
如果以上概念是你想要的,再來探討程式碼應該怎麼寫
對的,目前以公式函數的方法就是有作判別,如圖三
公式執行會作判別是否有同樣的流水號,如果有就要+1,不能重號
我的意思是用VBA寫出判斷,
你圖中A總店,box盒,DNS日用品,流水號001
先撇除流水號。
先在流水號表的標題欄位,用迴圈尋找
是否有a-box-dns,有就鎖定該欄位,再循列找出a-box-dns最後一筆是a-box-dns-00?
如果欄位尋找時,找不到就自動新增一欄
然後新增一列a-box-dns-001
BTW你把寫的VBA丟出來看看
如圖,我得意思是說 你應該要創建一個總表。
假設今天你要創建分店e-box-dns-
那你應該先用VBA寫迴圈到這裡判斷,是否第一欄有e-box-dns-
沒有就新增,e-box-dns-然後在該欄底下創建e-box-dns-
001=>回傳;
有就記錄該欄位位置。
再用另一個迴圈判斷該欄最後的數字是多少,該數字+1回傳
目前我是以公式函數去作判別,如圖三
從圖三的表中做完判別後,再將回傳的結果以公式顯示到圖一的流水碼與分單號,目前我表裡的VBA代碼只有按鈕新增與清除表格內容的功能,並沒有作編號判別的功能,因為我本身沒有學過VBA代碼,按鈕的代碼也是從網路上找來的,不是我自己寫的...
圖一 新建編號按鈕的VBA代碼
Private Sub CommandButton1_Click()
If MsgBox("請確認是否要新建?", vbYesNo, "確認視窗") = vbNo Then
Exit Sub
End If
Dim e As Integer, arr, aee
For e = 2 To 14
arr = Sheet4.Range("a65536").End(3).Row
If Sheet4.Cells(e, "a").Value <> "" Then
Sheet4.Cells(arr + 1, "a").Value = 工作表6.Cells(e, "a").Value
Sheet4.Cells(arr + 1, "b").Value = 工作表6.Cells(e, "a").Offset(0, 1).Value
Sheet4.Cells(arr + 1, "c").Value = 工作表6.Cells(e, "a").Offset(0, 2).Value
Sheet4.Cells(arr + 1, "d").Value = 工作表6.Cells(e, "a").Offset(0, 3).Value
Sheet4.Cells(arr + 1, "e").Value = 工作表6.Cells(e, "a").Offset(0, 4).Value
Sheet4.Cells(arr + 1, "f").Value = 工作表6.Cells(e, "a").Offset(0, 5).Value
Sheet4.Cells(arr + 1, "g").Value = 工作表6.Cells(e, "a").Offset(0, 6).Value
Sheet4.Cells(arr + 1, "h").Value = 工作表6.Cells(e, "a").Offset(0, 7).Value
End If
Next e
MsgBox "編號已建檔"
End Sub
圖一 清除表格內容按鈕的VBA代碼
Private Sub CommandButton2_Click()
'Updateby Extendoffice
Dim e As Integer, arr, aee
For e = 2 To 15
arr = Sheet2.Range("a65536").End(3).Row
If Sheet4.Cells(e, "a").Value <> "" Then
Sheet2.Range("H5").ClearContents
Sheet2.Range("M5").ClearContents
Sheet2.Range("C7").ClearContents
Sheet2.Range("H7", "J7").ClearContents
Sheet2.Range("C9").ClearContents
Sheet2.Range("M11", "N11").ClearContents
Sheet2.Range("C13", "G13").ClearContents
Sheet2.Range("J13", "O13").ClearContents
Sheet2.Range("C15", "O15").ClearContents
End If
Next e
MsgBox "表格已清空"
End Sub
看的出來你確實都不會,
不過我們還是先探討你想要達到的目的和流程,確認後我們再談程式碼
針對每一張表要做的動作敘述一下好了,
程式和公式先別看,就單存講一下妳要達到的目的
還又條件規則
比較複雜的部份就是流水號與分單號
在分單號之前的字串例如"A-BOX-DNS-A012-005-",當有第二筆完全相同的字串出現時,分單號就要跳號,例如第一筆是A-BOX-DNS-A012-005-0,當第二筆同樣出現"A-BOX-DNS-A012-005-"時,分單號就會跳為A,變成"A-BOX-DNS-A012-005-A";如果有第三筆完全相同字串出現時,分單號就要跳號為B,變成"A-BOX-DNS-A012-005-B",以此類推,其中英文I和O不使用
流水號的部分:只要輸入的字串判別相同時,流水號就要+1,例如訂單編號輸入後的字串為"A-BOX-SIY-A012-",假設流水號已到023號,那訂單編號就要變為"A-BOX-SIY-A012-024",分單號從0開始,完整訂單編號就是"A-BOX-SIY-A012-024-0"
流水號:判別流水號前所輸入的字串,有相同字串的作跳號(從001~999、A01~A99、B01~B99...以此類推,其中英文I和O不使用)
分單號:判別分單號前所輸入的字串,有相同字串的作跳號(從0、A、B、C、D、E...以此類推,其中英文I和O不使用)
我目前是用函數公式先在圖一輸入內容,在圖三用公式將"圖一輸入的內容變成字串與總表圖二已有的訂單編號"作判別,生成新的訂單編號到圖一(訂單編號:如果流水號相同,分單號要跳號;如果流水號前字串相同,那流水號要+1)
流水號建議是用兩個欄位比較好,一個作輸入,當輸入相同流水號,才能改變分單號,另一個欄位就是判別後回傳當前已編到的號碼
簡單理解只是把 分店,單位,品項類別,品項代號,流水號 合併作為訂單編號,再加析別重覆號便成?
類似這樣?
Sub test()
Worksheets("訂單編碼生成登表").Activate
gen_no = [h5] & "-" & [m5] & "-" & [c7] & "-" & [h7] & "-" & [c9]
If WorksheetFunction.CountIf(Worksheets("訂單總表").Columns("c"), "*" & gen_no & "*") = 0 Then
gen_no = gen_no & "-0"
Else
gen_no = gen_no & "-" & Chr(65 + WorksheetFunction.CountIf(Worksheets("訂單總表").Columns("c"), "*" & gen_no & "*") - 1)
End If
MsgBox gen_no
End Sub
VBA函數
Option Explicit
Function GenerateSerialNumber(rng As Range, ParamArray criteria() As Variant) As String
Dim ws As Worksheet
Set ws = rng.Parent
Dim count As Long
Dim i As Long
Dim isValid As Boolean
Dim result As String
For i = 3 To rng.Rows.Count
isValid = True
Dim j As Long
For j = LBound(criteria) To UBound(criteria) Step 2
If ws.Cells(i, criteria(j)) <> criteria(j + 1) Then
isValid = False
Exit For
End If
Next j
If isValid Then count = count + 1
Next i
If count > 0 Then
result = Application.WorksheetFunction.Text(count + 1, "000")
Else
result = "001"
End If
GenerateSerialNumber = result
End Function
Function GenerateSuffix(rng As Range, ParamArray criteria() As Variant) As String
Dim ws As Worksheet
Set ws = rng.Parent
Dim count As Long
Dim i As Long
Dim isValid As Boolean
Dim suffixChars As String
For i = 3 To rng.Rows.Count
isValid = True
Dim j As Long
For j = LBound(criteria) To UBound(criteria) Step 2
If ws.Cells(i, criteria(j)) <> criteria(j + 1) Then
isValid = False
Exit For
End If
Next j
If isValid Then count = count + 1
Next i
suffixChars = "0ABCDEFGHIJKLMNOPQRSTUVWXY"
If count > 0 Then
GenerateSuffix = Mid(suffixChars, count + 1, 1)
Else
GenerateSuffix = "N/A"
End If
End Function
然後,在單元格中使用以下公式:
流水號:
=GenerateSerialNumber(訂單總表!$C$3:$C$200000, 2, $B$2, 3, $B$3, 4, $B$4, 5, $B$5)
分號:
=GenerateSuffix(訂單總表!$C$3:$C$200000, 2, $B$2, 3, $B$3, 4, $B$4, 5, $B$5)