iT邦幫忙

1

Excel COUNTIFS函數想改用VBA代碼來判別數據流水碼

  • 分享至 

  • xImage

目前使用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"))))))))))))))))))))))))))))
https://ithelp.ithome.com.tw/upload/images/20230316/20158719t1FrEmv9XG.jpg
https://ithelp.ithome.com.tw/upload/images/20230314/20158719y0H8MUbMj1.jpghttps://ithelp.ithome.com.tw/upload/images/20230314/20158719QeuINwHoxB.jpg

實際資料非常多,圖片中的只是舉例說明,若有不清楚的部分可以問我,謝謝大家。

看更多先前的討論...收起先前的討論...
淺水員 iT邦大師 6 級 ‧ 2023-03-14 17:34:07 檢舉
這種業務需求是不是用 Access 或是其他資料庫軟體會比較好?
趁還在開發過程中不妨考慮一下
Bobo0509 iT邦新手 5 級 ‧ 2023-03-14 19:29:29 檢舉
未來可能會考慮用其他資料庫軟體,但現階段公司讓我們先用Excel做資料輸入整理,可能是怕花錢後又不會用...,所以現在只能先用這種方式作業,但因為公式長,資料又多,計算上時間就會跑很久(偶爾還會卡住),自己也沒有學過VBA,網上資料找了很多也試了很久都做不出來(應該是因為我不懂VBA的關係),只好到上面來求助大家QAQ
菩薩慈悲:不太明白菩薩您的需求,是要根據流水號和分單號產生唯一的識別號嗎?這個識別號就是第一個附圖的「訂單編號」欄位值嗎?
這個「訂單編號」應該至少是由「流水號」和「分號(分單號??還是分店號??)」來組成的吧?還有其他什麼部分的欄位資料來組成呢?是組成的全體不能重複,還是「流水號」和「分號」組成這部分不重複就好?

【能依據填入的內容作出比對後】,「 填入的內容 」在哪裡的內容呢?比對什麼呢?和什麼比對?

分號,是指分單號嗎?還是分店號(圖中沒有對應的)
【主要就是能依據填入的內容作出比對後,讓編號的流水號不重號,如果比對後有相同流水號,分號就要自動跳號(從0開始,A、B、C以此類推,跳過英文I和O),不能完全一樣!】
「分號就要自動跳號」,那跳號之後和流水號及至其他組成成分組成後的樣子是怎樣呢?可不可以舉例詳明?

末學的理解是菩薩您好像只是要組成一個可供識別的ID編號(是否即是訂單編號)就好?(故名為「訂單編碼」生成登記表)
那這樣的ID是由哪幾毎部分組成呢?您都未說明白。末學實在不知該如何實作。感恩感恩 南無阿彌陀佛

【分號就要自動跳號(從0開始,A、B、C以此類推……】我看您第2圖的例子,為什麼0之後不編1而直接編A呢?到底是要按照怎樣的規則或有怎樣的限制,都當明白才好。感恩感恩 南無阿彌陀佛
為什麼第2圖所顯示的內容 第一列前後相同,而第二列前面(末)是A,後面的卻是B?我想這A、B和第一列末的0就是「分號」(分單號??)吧?

如果可以,是否用 Google Meet 等來討論看看,比較好掌握您實際的需求。勝過單憑文字千萬倍吧。此問題我已追蹤,能力若可,可以一試。感恩感恩 南無阿彌陀佛
菩薩慈悲:末學先以我所理解的做到取得流水號的部分,詳此檔:
https://www.dropbox.com/s/xkqijwg12mh11jk/%E5%8F%96%E5%BE%97%E5%8F%AF%E7%94%A8%E7%9A%84%E6%B5%81%E6%B0%B4%E8%99%9F%E8%88%87%E5%88%86%E5%96%AE%E8%99%9F.xlsm?dl=0
您試操作於此活頁簿檔的D8儲存格(目前程式的觸發點只寫在這個儲存格更新之後),如在C8值為「SIY」時在D8輸入了「A035」,因為已有了「001」,故在E8會自動顯示「002」,表示001的流水號已重號(用過、存在了)。同理,若C8值是「DNS」而您在D8同樣輸入「A035」,因為「DNS」的「A035」尚無用到「001」,故E8的儲存格會自動顯示為001以備用……以此類推。至於如何擴展,程式寫在哪裡、在哪兒觸發,都待後續的討論。……總之您先試試,如果是菩薩您想要的,我再繼續做下去。感恩感恩 南無阿彌陀佛

ps.記得下載開啟後要啟用巨集,否則不會有作用,因為程式無法執行。(想您接觸過VBA 應該明白。阿彌陀佛)

ps. 為防萬一,如果說遺失參考,請到VBA 編輯器中「工具→設定引用項目」要找到「Microsoft Scripting Runtime」勾選引用,因用了它裡面的Dictionary 物件類別實作故 感恩感恩 南無阿彌陀佛
Bobo0509 iT邦新手 5 級 ‧ 2023-03-15 18:54:12 檢舉
1.「流水號」和「分號=分單號」組成這部分不重複就好?可參考圖二的訂單編號欄位
第一行和第二行的的訂單編號只有分單號不同,前面完全相同;流水號如果相同,分單號就要跳號不能重複;如果流水號前的字串和分單號相同,流水號就要自動+1
2.【能依據填入的內容作出比對後】,「 填入的內容 」在哪裡的內容呢?比對什麼呢?和什麼比對?
填入的內容是從圖一的登記表去輸入的,主要就是比對流水號以前的那些字串和分單號
3.【分號就要自動跳號(從0開始,A、B、C以此類推……】,這規定不是我訂的...我也很想知道為什麼不接著1、2、3...照順序下來,而是從0跳A....
4.為什麼第2圖所顯示的內容 第一列前後相同,而第二列前面(末)是A,後面的卻是B?我想這A、B和第一列末的0就是「分號」(分單號??)吧?
是的,就是分單號,抱歉可能我沒表達清楚,產生誤解

謝謝您這麼認真用心的回覆我!!
我會先試試看您提供的檔案,非常感謝!
ok 等菩薩您的消息了。其他菩薩的解答當也不錯。若有需要,又沒人幫得上忙,末學能力也可及的話,再跟末學我說。
回覆菩薩您的:
1、2. 這兩點和我想的一樣,我實作的也做到了。只有分單號的部分還未實作。您不妨照我給您的excel檔去試試,應該就像那樣。只是不是從圖一的表單介面輸入框輸入,而是先用「D8」來作輸入點(模擬那個表單的輸入框)測試;如果確實是您所需要的,我們再完成實作,再套用到您對應的介面應該就可以了。
3. 我自己實作後體會到應該是和流水號的000~999 又接著從A01~ B01……有關。大概是制定編號規則的人把第一位獨立出來了,也就是實際上不是000 或999這樣的數字,而是0+01~99…… 而 9+01~99 這樣的結構,所以才會是0完了接著A,這就和分單號的規則是一致的了,所以流水號裡的A、B ……也是 A+01~99…… B+01~99…… 這樣的邏輯吧。也就是說,這三位數的流水號的結構其實是前一位和後二位銜接的關係,真正的數字編號是在後二位上,從01~99,而第一位則是從0、A……這樣下去的,實際就是把它當作字串在處理。就和分單號的一致了。只是不知道為什麼又要跳過字母I和O,難道是這兩個字母和數字 1 和 0 太像了嗎?以防混亂誤看,才會想避免用上這2個字母編號?我想是這樣的吧。否則也不知道什麼原因了。
4. 那我的理解就沒錯了。我目前的實作也是按此邏輯去實現的。那菩薩您應該就可以看到跑出來的結果(目前只有流水號部分)應該就是照您預期想要的方式來呈現了吧。您不妨多變換輸入條件去測試看看。流水號的部分OK了,接著分單號的部分也就會迎刃而解了。祝您順利。感恩感恩 南無阿彌陀佛
Bobo0509 iT邦新手 5 級 ‧ 2023-03-16 14:25:23 檢舉
為什麼又要跳過字母I和O,難道是這兩個字母和數字 1 和 0 太像了嗎?
應該是的!
謝謝您的回覆,目前測試流水號的部分是正常的!正是我需要的功能!

接下來就是分單號的部分,如果前面輸入的字串與流水號都一樣,分單號就要跳號,例如已有一筆訂單編號為A-BOX-DNS-A078-003-0,若輸入的是A-BOX-DNS-A078-003,那分單號就要變成A,訂單編號就會是A-BOX-DNS-A078-003-A
Bobo0509菩薩慈悲:程式大致完成了。下載網址如前,若是您所需的,我就要來「回答」了。操作如前。(在 D 欄輸入,則自動在流水號與分單號欄輸入。在流水號欄輸入則自動輸入分單號。)
請您多多測試,特別是大資料時的效能與正確性。感恩感恩 南無阿彌陀佛

若一切OK,我就要把程式碼貼過來回答了。
如果還有問題最好是用 Google Meet 等會議軟體以螢幕分享的方式來討論才好,光打字的實在太累了,也無法及時掌握菩薩您實際的狀況。感恩感恩 南無阿彌陀佛


(以下是我這次修改前的原稿,僅作參考,也可以明白我寫這個程式所應用的原理。)

只是這次可在兩個橘色儲存格內輸入測試(其實在這兩欄內任何一列輸入都可以,只要讓J欄與A到F欄的資料保持連動與正確就可(輸入的新記錄那列 J 欄不要有資料就好),因為既有編號我是以「J」欄為參照,而它的值的來源您看其內的公式當可明白。)

這次結果我改在G、H 二欄(如紅色字處),這是因為和工作表事件觸發點有關的緣故,不想影響測試過程,一點權宜措施。反正最後也是要改套用到菩薩您實際的介面才行。這就先作顯示結果以供檢驗的參考就好。

在D欄的儲存格輸入完後,程式即會自己找到可用的流水號與分單號,分別顯示在同列的G、H兩欄中。若在 D 輸入完後,再在 E 欄的儲存格輸入流水號,一樣會自動找到可用的分單號而顯示在H欄同列的儲存格中。菩薩您可以在前面多加幾列記錄測試看看(尤其可以測試在大資料(如上萬筆時)執行的速度。如這檔案就是我用22萬筆測試(當然其中有很多重複的編號,可能不太準)大概最慢約5秒鐘會出來結果)。只要注意 J 欄的現有編號要與前6欄對應一致就好。原理就是如此。以J作為比對查找的參考基,只要J沒有的,這程式就會自動在G、H分別顯示出可用的流水號與分單號來。且會自動跳過英文字母I、O為開頭的流水號,與I、O為值的分單號了。
Bobo0509 iT邦新手 5 級 ‧ 2023-03-20 16:05:30 檢舉
孫守真任真甫大大您好,您提供的檔案我測試過,有小小問題想要請教您,流水號的部分請問可以分為兩個欄位嗎?一個欄位是輸入,另一個欄位是判別現有編號後作回傳,因為目前我用函數公式做的登記表是有兩個欄位(如圖一所示),白底黑字的流水號是手動輸入(主要是同流水號要跳分單號時,流水號就需要手動輸入,這樣分單號經過判別,發現有相同的字串後就會自動跳號),紅字的流水號,是與總表判別字串後,顯示當前總表裡已經編到的流水號;這樣才能規避我之前曾遇過的問題,也是在您VBA代碼裡發現的同樣問題(當我手動改流水號時,假設編號是A-BOX-DNS-A056-001-0,在流水號欄位裡重複輸入001,分單號欄位會一直跳號,同樣流水號輸入幾次,分單號就跳幾次A、B、C...一直往下跳)
若不了解可以再問我,謝謝您!您做得已比我想像的要棒很多很多了!
Bobo0509菩薩慈悲。當然可以啊,這也是在程式設計上必須要有的考量與彈性(可擴展及方便維護等等)。我本來做的就是在另一個儲存格顯示結果,而不是直接顯示在要輸入的地方(如我前面回應後面提到的的樣子)。只是我不太明白您究竟的情況是怎麼樣的,而每次都得打字再往返,也未必能確切掌握您的需求,對於在程式設計上要怎麼規畫也會造成偌大的困擾,所以才希望能盡量利用會議軟體以螢幕分享的方式來演示操作或展示給我看您實際的需求,這樣會更清楚也更具體即時多了。此回應後,我會先作回答,因為我發現這裡在回答裡討論會比在您原來這個發問的帖子下面回應靈活得多了,也有更多可用的格式設定,如程式碼標識、排版等等,且可以標註對象,方便讓對方知道您已作出回應了(會在「即時通知中心」出現「留言給您」的通知)。如果您仍不便用會議軟體討論的話,就請您移駕至我的回答下回應討論,當然,如果我仍無法及時精確地掌握到您的需求的話,也請您能諒解在文字溝通上難免會有的侷限性。感恩感恩 南無阿彌陀佛
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
1
孫守真任真甫
iT邦研究生 4 級 ‧ 2023-03-21 00:56:55
最佳解答

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 iT邦新手 5 級 ‧ 2023-03-21 16:34:41 檢舉

孫守真任真甫大大,首先必須感謝您這麼有耐心的一再修改程式來幫助我,真的非常感謝!
為了讓您能夠了解我當初製作這個表時的邏輯想法,再此回覆末尾附上我的範例檔案供您參考。
其實您上述提到的部分已與我當初用公式製作時的想法很接近,要使用者可以輸入流水號,但不能輸入分單號。在使用者輸入完流水號後,才由程式自動賦予不重號的分單號,是這樣嗎???這樣要怎麼確定使用者真的輸入完流水號,而不再改變了呢?還是希望使用者可以輸入改變
我之前一直沒有提到的地方,是關於編號自動生成的部分,再說這部分之前,先解答您上述的疑問"要使用者可以輸入流水號,但不能輸入分單號。在使用者輸入完流水號後,才由程式自動賦予不重號的分單號,是這樣嗎???這樣要怎麼確定使用者真的輸入完流水號,而不再改變了呢?還是希望使用者可以輸入改變"

  1. 為什麼流水號會有兩個欄位:因為會出現相同流水號的情況,當有相同流水號時,若流水號無法手動修改,分單號就不會變化跳號(分單號是依據流水號來作判別),因此需要有兩個欄位來做變化,一個是手動輸入,另一個欄位則是程式依據已輸入的字串作判別後**"顯示當前已編到的流水號碼"**
    真正決定流水號的是手動輸入的欄位(圖一的C9欄位),程式判別那欄只作當前最新編號的顯示(圖一的G9欄位)
  2. 同樣的想法,其實分單號也是兩個欄位(都是由程式判別,使用者無法手動修改,這是為了避免重號),您在圖一所看到的紅字非白底的是程式判別後顯示當前最新分單號(圖一的M9欄位),
    接下來要說的就是編號生成的部分,分單號的第二個欄位,就是在編號生成的這個欄位裡出現(圖一的C11欄位裡編號的最後一碼),這裡同樣是由程式判別,依據C9欄位的流水號作判別後產生變化,當C9欄位所輸入的流水號與總表(圖二)相同時,這個欄位所生成的編號,在最後一碼分單號的部分就要自動跳號,若是新的流水號,那分單號就從"0"開始

我自知自己的表達能力欠缺,故詳細的功能您可以透過底下的範例檔案來測試,若還有疑問,是否能與您再約個時間透過線上會議之類的方式來做說明,以上,謝謝您。

訂單編號範例檔案

Bobo0509菩薩慈悲:

今天(3/23)晚上有空嗎?先會議解決以下問題,好嗎?現在Google Meet有時間限制了。所以先約好時間,您發起會議給我連結。感恩。我晚上7點再來看您回覆沒。或您方便時,到臉書Messenger敲我。感謝

1.【真正決定流水號的是手動輸入的欄位(圖一的C9欄位),程式判別那欄只作當前最新編號的顯示(圖一的G9欄位)】
所以C9是顯示到底是可以編的新號(容許使用者輸入的新號,即尚未編入編號者),還是最後編的舊號?(請明示!)
2.同樣的【在圖一所看到的紅字非白底的是程式判別後顯示當前最新分單號(圖一的M9欄位),】所以,M9也是可以用的新號,而不是舊有最後的一號嗎?
★我看您圖一與範例檔分明G9與M9是舊有最後(或最近)的流水號與分單號嘛!為什麼說「顯示當前最新分單號」?您所謂的「最新」是舊有的最近(新)嗎?不是可編的新號吧。請以上2個疑問一定要先搞定,我才會繼續下去,免得又白做了。(我有空妳又還沒回覆的話,會用舊有的最後一筆編號來算,而不是顯示新的可輸入的編號})感恩感恩 南無阿彌陀佛
3.為什麼分單號還要分兩個欄位?明明一個欄位便可敷用了……這裡末學還是感到很困惑也奇怪!您所謂的兩個欄位,第2個是N9(與O9合併?)嗎。但您回應說明裡又說和C11有關(是C11D11到G11合併或跨欄的欄位吧?)這個C11為什麼不用「訂單總表」工作表中的訂單編號欄位值或訂單表顯示內容欄位半形空格前的值回傳就好?還有「訂單總表」為什麼訂單編號欄位和訂單表顯示內容欄位所顯示的值又不同了呢?前者是舊號,後者是取得使用輸入後新編而加入記錄的編號嗎?請菩薩您務必先回答以上問題。感恩感恩

●有實際的範例檔就好操作多了,不但能具體掌握您應用程式的流程邏輯,我也可以直接把程式碼掛上去測試。您早該給我了。我先試試吧。但可能得請菩薩您稍安勿躁,畢竟前面徒勞往返白費了許多心思,我還有別的要忙,餘暇才能分神理會。想能見諒。感恩感恩 南無阿彌陀佛

4.我看到「CommandButton1_Click」,這個按鍵就是使用者確定輸入完時的動作嗎?也就是按下就不會再更改編號記錄中的記錄了。是嗎?而不是在C9更動/輸入後。是這樣嗎?這很重要,就是我前面提到的程式碼更新編號記錄的時機點。

Bobo0509 iT邦新手 5 級 ‧ 2023-03-23 16:44:25 檢舉

> 今天晚上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 同步同時,還是在流水號有資料輸入後才顯示呢?又是否要依流水號的輸入情況,再依其輸入之值判斷分單號的顯示值呢 請菩薩您明示
確定上述情況,我就大致懂了。感恩感恩 南無阿彌陀佛

Bobo0509 iT邦新手 5 級 ‧ 2023-03-23 19:14:18 檢舉

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
Bobo0509 iT邦新手 5 級 ‧ 2023-03-24 13:23:09 檢舉

孫守真任真甫孫博士您好,您所提供的代碼我早上有作一些測試,但有一些小小的問題想再請教您
1.當登記表的流水號與分單號在總表裡沒有記錄時,能不能在紅字顯示欄位裡顯示為"N/A"(表示無資料的意思),我自己有嘗試改動,目前已改成能顯示為"N/A"了[您原來的代碼顯示會是初始值"流水號為001"和"分單號為0"]
但分單號在N9欄位裡會顯示為"當前為N/A版次",這個我實在不知道要從哪裡修改或新增IF判別,怕改到不該改的地方(如下圖所示)
https://ithelp.ithome.com.tw/upload/images/20230324/20158719XZCQ5eYCWQ.png
我希望當分單號在總表無資料記錄時,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%都看不太明白),看到您如此費心費力只為幫助我,真的非常非常感謝您。
https://ithelp.ithome.com.tw/upload/images/20230324/20158719jTMewsFpO9.png

Bobo0509菩薩慈悲:看太多文字我都發麻了,也未必就能掌握您確切的需要,且這裡內容太多,頁面切換又太不方便,不如會議討論交流來得簡潔有效,想昨晚對談之效益已很顯著明確。您既然不急,我時間又較彈性,可否請您有任何問題和我約個時間如昨晚一樣快刀斬麻,才能大快人心。這些多文字部分,我就先隨自己方便,加減處理了。感恩感恩 南無阿彌陀佛

Bobo0509 iT邦新手 5 級 ‧ 2023-03-24 14:16:15 檢舉

孫守真任真甫孫博士您好,我會在簡訊裡與您約時間以會議方式交流,再次感謝您的幫助與回復,謝謝您。

0
rain_yu
iT邦研究生 5 級 ‧ 2023-03-15 08:31:37

如果想改用VBA查詢是否重號
(大義上)創立已有的流水號sheet,
然後直接跳到底端看最後的流水號是多少,
新增資料=>流水號+1,回傳流水號於sheet

舉例來說,A總店,box盒,DNS日用品,流水號001
你應該先創建一欄a-box-dns-
這欄底下會有a-box-dns-001、a-box-dns-002、a-box-dns-003...。
直接跳到該欄底下找到最後的編號,+1就是新的流水號

如果以上概念是你想要的,再來探討程式碼應該怎麼寫

看更多先前的回應...收起先前的回應...
Bobo0509 iT邦新手 5 級 ‧ 2023-03-15 18:40:42 檢舉

對的,目前以公式函數的方法就是有作判別,如圖三
公式執行會作判別是否有同樣的流水號,如果有就要+1,不能重號

rain_yu iT邦研究生 5 級 ‧ 2023-03-16 11:56:46 檢舉

我的意思是用VBA寫出判斷,
你圖中A總店,box盒,DNS日用品,流水號001
先撇除流水號。
先在流水號表的標題欄位,用迴圈尋找
是否有a-box-dns,有就鎖定該欄位,再循列找出a-box-dns最後一筆是a-box-dns-00?
如果欄位尋找時,找不到就自動新增一欄
然後新增一列a-box-dns-001

BTW你把寫的VBA丟出來看看

rain_yu iT邦研究生 5 級 ‧ 2023-03-16 12:05:36 檢舉

https://ithelp.ithome.com.tw/upload/images/20230316/20147438TI6pjZDQJ4.png
如圖,我得意思是說 你應該要創建一個總表。
假設今天你要創建分店e-box-dns-
那你應該先用VBA寫迴圈到這裡判斷,是否第一欄有e-box-dns-
沒有就新增,e-box-dns-然後在該欄底下創建e-box-dns-
001=>回傳;

有就記錄該欄位位置。
再用另一個迴圈判斷該欄最後的數字是多少,該數字+1回傳

Bobo0509 iT邦新手 5 級 ‧ 2023-03-16 14:58:38 檢舉

目前我是以公式函數去作判別,如圖三
從圖三的表中做完判別後,再將回傳的結果以公式顯示到圖一的流水碼與分單號,目前我表裡的VBA代碼只有按鈕新增與清除表格內容的功能,並沒有作編號判別的功能,因為我本身沒有學過VBA代碼,按鈕的代碼也是從網路上找來的,不是我自己寫的...

Bobo0509 iT邦新手 5 級 ‧ 2023-03-16 15:02:51 檢舉

圖一 新建編號按鈕的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

rain_yu iT邦研究生 5 級 ‧ 2023-03-17 08:25:28 檢舉

看的出來你確實都不會,
不過我們還是先探討你想要達到的目的和流程,確認後我們再談程式碼
針對每一張表要做的動作敘述一下好了,
程式和公式先別看,就單存講一下妳要達到的目的
還又條件規則

Bobo0509 iT邦新手 5 級 ‧ 2023-03-20 16:21:56 檢舉

比較複雜的部份就是流水號與分單號

  1. 在分單號之前的字串例如"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不使用

  2. 流水號的部分:只要輸入的字串判別相同時,流水號就要+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不使用)

Bobo0509 iT邦新手 5 級 ‧ 2023-03-20 16:28:06 檢舉

我目前是用函數公式先在圖一輸入內容,在圖三用公式將"圖一輸入的內容變成字串與總表圖二已有的訂單編號"作判別,生成新的訂單編號到圖一(訂單編號:如果流水號相同,分單號要跳號;如果流水號前字串相同,那流水號要+1)
流水號建議是用兩個欄位比較好,一個作輸入,當輸入相同流水號,才能改變分單號,另一個欄位就是判別後回傳當前已編到的號碼

0
blanksoul12
iT邦研究生 5 級 ‧ 2023-03-15 09:05:06

簡單理解只是把 分店,單位,品項類別,品項代號,流水號 合併作為訂單編號,再加析別重覆號便成?
類似這樣?

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
blanksoul12 iT邦研究生 5 級 ‧ 2023-03-15 09:14:45 檢舉

當然還沒完,還要在總表那邊定位放下你要的其他東西.其實不難,難在往後你們要怎樣處理總表的資料.而且 excel 的保安及保存很兒戲的.
VBA 這事,要在日常工作中取例子去完成.

blanksoul12 iT邦研究生 5 級 ‧ 2023-03-15 09:17:02 檢舉
Bobo0509 iT邦新手 5 級 ‧ 2023-03-15 18:56:27 檢舉

好的,感謝您的回答,我會去試試的,今天也有自己試著寫VBA,但可能是我寫的方式不對,一直無法運行...

0
JamesDoge
iT邦高手 1 級 ‧ 2023-03-15 09:15:24

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)
Bobo0509 iT邦新手 5 級 ‧ 2023-03-15 18:58:22 檢舉

謝謝大大的回答,明天上班我會一個個套進去試試的,謝謝您們

Bobo0509 iT邦新手 5 級 ‧ 2023-03-16 14:53:21 檢舉

JamesDoge大大,我想請教您一下,請問是我VBA代碼位置放錯了嗎?
流水碼與分單號沒有變...
如圖這樣
https://ithelp.ithome.com.tw/upload/images/20230316/20158719sLOqmTN7yR.png
https://ithelp.ithome.com.tw/upload/images/20230316/20158719stfkxjIaQu.png

我要發表回答

立即登入回答