返回列表 上一主題 發帖

依未結PO及要出貨數量,自動換算要出貨的PO及數量

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
0  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
1# 跳轉到 »
發表於 2012-9-22 07:15 | 顯示全部帖子
本帖最後由 luhpro 於 2012-9-22 07:16 編輯
此問題原發於一般區，但如以巨集是否也可自動套取，故再次將同問題發於此區中，請各位大大指教，先謝謝各位 ...
p6703 發表於 2012-9-21 10:25
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.   Dim iRow%
  3.   Dim rStuff As Range
  4.   Dim bChecked As Boolean
  5.   Dim vPo, vBalance
  6.   
  7.   Set vPo = Sheets("未結PO")
  8.   
  9.   With Target.Parent '於 Sheets("出貨數量")
  10.     If .Cells(Target.Row, 2) <> "" And .Cells(Target.Row, 3) <> "" Then
  11.       iRow = IIf(.Cells(2, 5) = "", 2, .Cells(2, 5).End(xlDown).Row + 1)
  12.       vBalance = .Cells(Target.Row, 3)
  13.       Set rStuff = vPo.[A2] ' 於 Sheets("未結PO")
  14.       
  15.       Do While .Cells(Target.Row, 2) <> rStuff ' 找到該客戶
  16.         Set rStuff = rStuff.Offset(1) ' 移到下一筆資料
  17.       Loop
  18.       
  19.       Do
  20.         Do While rStuff.Offset(, 3) <> 0    ' 還有尚未出貨的資料
  21.           If vBalance > rStuff.Offset(, 3) Then ' 尚未出貨數量大於當下未交數
  22.             Application.EnableEvents = False
  23.               .Cells(iRow, 5) = .Cells(Target.Row, 1) ' 日期
  24.               .Cells(iRow, 6) = .Cells(Target.Row, 2) ' 客戶料號
  25.               .Cells(iRow, 7) = rStuff.Offset(, 1) ' 客戶PO
  26.               .Cells(iRow, 8) = rStuff.Offset(, 3) ' 出貨數
  27.               vBalance = vBalance - rStuff.Offset(, 3)
  28.               rStuff.Offset(, 3) = 0
  29.               iRow = iRow + 1
  30.             Application.EnableEvents = True
  31.           Else
  32.             Application.EnableEvents = False
  33.               .Cells(iRow, 5) = .Cells(Target.Row, 1) ' 日期
  34.               .Cells(iRow, 6) = .Cells(Target.Row, 2) ' 客戶料號
  35.               .Cells(iRow, 7) = rStuff.Offset(, 1) ' 客戶PO
  36.               .Cells(iRow, 8) = vBalance  ' 出貨數
  37.               rStuff.Offset(, 3) = rStuff.Offset(, 3) - vBalance
  38.             Application.EnableEvents = True
  39.             Exit Sub ' 尚未出貨數量等於0則跳出
  40.           End If
  41.         Loop
  42.         Set rStuff = rStuff.Offset(1) ' 移到下一筆資料
  43.       Loop Until .Cells(Target.Row, 2) <> rStuff
  44.     End If
  45.   End With
  46. End Sub
複製代碼
自動核消表-Ans.zip (12.04 KB)

Sheets("出貨數量") 的右邊是備份參考用的資料.

TOP

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
0  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
2#
發表於 2012-9-27 19:15 | 顯示全部帖子
超感謝luhpro兄，原來巨集也可以這麼用，輸入符合的資料後自動秀出，真是讓小弟”大開眼界”
只是小弟所學 ...
p6703 發表於 2012-9-22 22:23

當判斷出貨資料輸入完後,
日期與料號部分是從輸入區直接帶過去,而先以料號為 Key 依序往下找符合的未結PO資料(當下未交數 不是 0 的),
找到後將客戶 PO 帶過去, 再將計算結果分別帶入 當下未交數 與 出貨數, 判斷是否需出貨數都已完成, 若未完成則繼續上述循環.

luhpro兄，小弟實際執行發現有以下問題，再煩請協助解惑，謝謝..
1.新增的筆數資料不會捉到
因PO會陸續 ...
p6703 發表於 2012-9-24 18:12

這確實是該程式沒有處理的部份,
1. 若填資料時沒有將 期初未交數 也加填到 當下未交數, 那麼 當下未交數 是 0 自然就不會有新出貨資料了.
可於 Sheets("未結PO")加上 :
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.   With Target.Parent '於 Sheets("未結PO")
  3.     If .Cells(Target.Row, 1) <> "" And .Cells(Target.Row, 2) <> "" And .Cells(Target.Row, 3) <> "" Then
  4.       With .Cells(Target.Row, 3)
  5.         .NumberFormat = "#,##0_ "
  6.         .Offset(, 1).NumberFormat = "#,##0_ "
  7.         .Offset(, 1) = .Value
  8.       End With
  9.     End If
  10.   End With
  11. End Sub
複製代碼
2. 這個處理上需要較多的動作,需要再想一下, 晚點再Post.
luhpro兄，小弟開始輸入資料，才第二筆就跑出以下錯誤，確定資料並未打錯，而"未結PO"當下未交數也沒錯，不 ...
p6703 發表於 2012-9-25 11:13

這可能是你資料列數超過 Integer 可處理範圍, 我將 iRow 改成 lRow 如下:
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.   Dim lRow As Long
  3.   Dim rStuff As Range
  4.   Dim bChecked As Boolean
  5.   Dim vPo, vBalance
  6.   
  7.   Set vPo = Sheets("未結PO")
  8.   
  9.   With Target.Parent '於 Sheets("出貨數量")
  10.     If .Cells(Target.Row, 2) <> "" And .Cells(Target.Row, 3) <> "" Then
  11.       lRow = IIf(.Cells(2, 5) = "", 2, .Cells(2, 5).End(xlDown).Row + 1)
  12.       vBalance = .Cells(Target.Row, 3)
  13.       Set rStuff = vPo.[A2] ' 於 Sheets("未結PO")
  14.       
  15.       Do While .Cells(Target.Row, 2) <> rStuff ' 找到該客戶
  16.         Set rStuff = rStuff.Offset(1) ' 移到下一筆資料
  17.       Loop
  18.       
  19.       Do
  20.         Do While rStuff.Offset(, 3) <> 0    ' 還有尚未出貨的資料
  21.           If vBalance > rStuff.Offset(, 3) Then ' 尚未出貨數量大於當下未交數
  22.             Application.EnableEvents = False
  23.               .Cells(lRow, 5) = .Cells(Target.Row, 1) ' 日期
  24.               .Cells(lRow, 6) = .Cells(Target.Row, 2) ' 客戶料號
  25.               .Cells(lRow, 7) = rStuff.Offset(, 1) ' 客戶PO
  26.               .Cells(lRow, 8) = rStuff.Offset(, 3) ' 出貨數
  27.               vBalance = vBalance - rStuff.Offset(, 3)
  28.               rStuff.Offset(, 3) = 0
  29.               lRow = lRow + 1
  30.             Application.EnableEvents = True
  31.           Else
  32.             Application.EnableEvents = False
  33.               .Cells(lRow, 5) = .Cells(Target.Row, 1) ' 日期
  34.               .Cells(lRow, 6) = .Cells(Target.Row, 2) ' 客戶料號
  35.               .Cells(lRow, 7) = rStuff.Offset(, 1) ' 客戶PO
  36.               .Cells(lRow, 8) = vBalance  ' 出貨數
  37.               rStuff.Offset(, 3) = rStuff.Offset(, 3) - vBalance
  38.             Application.EnableEvents = True
  39.             Exit Sub ' 尚未出貨數量等於0則跳出
  40.           End If
  41.         Loop
  42.         Set rStuff = rStuff.Offset(1) ' 移到下一筆資料
  43.       Loop Until .Cells(Target.Row, 2) <> rStuff
  44.     End If
  45.   End With
  46. End Sub
複製代碼

TOP

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
0  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
3#
發表於 2012-10-2 00:06 | 顯示全部帖子
回復 5# p6703
因為程式落落長, 所以就不再貼上詳細程式的文字內容直接提供附檔囉.
自動核消表-Ans2.zip (27.79 KB)

操作方式 :
對輸入區中對任一 "有資料" 的某個儲存格按 "滑鼠右鍵" 會啟動資料異動視窗,
於此視窗中可執行針對該儲存格當筆資料異動, 如 : 插入一列、刪除該列 或是針對各欄內容作異動調整.

程式運作的原理是先還原後再備份出貨資料,
直到標的儲存格那一筆資料(含),
接著依需求異動欲重貼的首筆資料,
最後再分別將所備份的出貨資料逐個貼上去,
如此就能達到需求囉.


兩 Sheets 右側為參考資料,
日期輸入沒有套用月曆物件,
且此類錯誤並未設置錯誤處理程式.

另於 "輸入區" 中無資料區域的輸入方式與一般 Excel 輸入方式相同,
每輸完一筆資料就會直接做出貨配比,
而目前於此方式下並未設計資料檢核機制,
故而這裡可能會發生輸錯資料造成錯誤的情形.

還有目前該程式並無 "非新增資料" 同時異動兩欄位資料的功能.

TOP

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
0  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
4#
發表於 2012-10-2 21:17 | 顯示全部帖子
本帖最後由 luhpro 於 2012-10-2 21:19 編輯
回復  luhpro
Private Sub Worksheet_Change 是這 "未結PO" 工作表的觸動事件
GBKEE 發表於 2012-10-2 10:16

嗯...
我這樣寫是依據我之前有過不好的經驗而調整過來的,
此前有遇到使用者反應作業中會亂跳錯誤訊息.

後來經過測試才發現若一 Excel 檔案正在運作 VBA 程式(即並非正在等待使用者的操作),
而使用者又因有其他需求開啟或切換到另一個 Excel 程式(或是使用者想趁程式運作中的等待時間, 切換到另一個 Sheet 檢視資料)時,
像上述那樣不指定要作用的物件的情形,
有機率會發生索引(即所作用的物件並非程式設計時所預期的那個)錯誤的狀況.

我覺得這似乎是因為 Windows 多工而 Excel VBA 又可以作用在不同的檔案或 Sheet 間所導致的問題,
所以後來我都會用 With 指定作用物件於其內再以 . 來索引,
以避免類似困擾的發生.

這是我的理解,也許不一定對,
但是那時這樣做過後該問題就不再發生了.
PS: 當時的環境是 Windows XP + Office 2000 Professional

TOP

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
0  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
5#
發表於 2012-10-3 22:55 | 顯示全部帖子
本帖最後由 luhpro 於 2012-10-3 22:57 編輯
luhpro兄，小弟實際測試，除了感謝費時的修改，發現新輸入"未結PO"的資料無法再於"出貨數量"中秀出
例： 於出貨數中將原PO數都核消完後，就無法再產生新PO出貨明細了
p6703 發表於 2012-10-3 10:27

這是因為在 Sheets("未結PO") 有異動時 並沒有做適當的處理:
1. 將客戶料號加入 UserForm 裡客戶料號的下拉式選單中,以備後續作業中可以正常選擇到該料號.
2. 將使用者輸入的 "期初未交數" 代入至 "當下未交數" 內,
因為儲存格沒有輸入資料時其值為 0 ,
故而 "當下未交數" 是 0 的新增項目,
自然就無法正常參與出貨序列了.

修改後程式如下:
自動核消表-Ans3.zip (33.34 KB)

每輸完一筆資料就會直接做出貨配比,==>當下未交數是會自動秀出KEY IN的數量

不懂, 是程式的反應有問題嗎?

TOP

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
0  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
6#
發表於 2012-10-6 01:39 | 顯示全部帖子
luhpro兄依提供的自動核消表-Ans3中在未結PO第34列直接增加一筆35451 500,000（此原未結已為0），然後再於出 ...
p6703 發表於 2012-10-4 12:09

修正一行內容 :

      Do Until .Cells(Target.Row, 2) = rStuff And rStuff.Offset(, 3) <> 0  ' 找到該客戶

自動核消表-Ans4.zip (33.55 KB)

TOP

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
0  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
7#
發表於 2012-10-8 22:46 | 顯示全部帖子
本帖最後由 luhpro 於 2012-10-8 22:47 編輯
回復  luhpro
    我是路人~~~~~
    我試著重新把已輸入的刪除
    測試的結果如下圖.......大家看圖 ...
softsadwind 發表於 2012-10-6 17:07

修改程式如下:
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.   Dim lRow As Long
  3.   Dim rStuff As Range, rTemp As Range
  4.   Dim bChecked As Boolean
  5.   Dim vPo, vBalance
  6.   
  7.   With Target.Parent '於 Sheets("出貨數量")
  8.     Set vPo = Sheets("未結PO")
  9.     Set rTemp = .Cells(Target.Row, 3)
  10.   
  11.     If .Cells(Target.Row, 2) <> "" And rTemp <> "" And Target.Column < 4 Then
  12.       lRow = .Cells(Rows.Count, 5).End(xlUp).Row + 1
  13.       vBalance = rTemp
  14.       Set rStuff = vPo.[A2] ' 於 Sheets("未結PO")
  15.       
  16.       'Do Until .Cells(Target.Row, 2) = rStuff And rStuff.Offset(, 3) <> 0  ' 找到該客戶
  17.       '  Set rStuff = rStuff.Offset(1) ' 移到下一筆資料
  18.       'Loop
  19.       
  20.       Do ' Loop Until rStuff = ""
  21.         Do Until (.Cells(Target.Row, 2) = rStuff And rStuff.Offset(, 3) <> 0) Or rStuff = ""  ' 找到該客戶或已沒資料
  22.           Set rStuff = rStuff.Offset(1) ' 移到下一筆資料
  23.         Loop
  24.         
  25.         Do While rStuff.Offset(, 3) <> 0    ' 還有尚未出貨的資料
  26.           If vBalance > rStuff.Offset(, 3) Then ' 尚未出貨數量大於當下未交數
  27.             Application.EnableEvents = False
  28.               With .Cells(lRow, 5)
  29.                 .NumberFormat = "yyyy/m/d"
  30.                 .Value = .Parent.Cells(Target.Row, 1) ' 日期
  31.               End With
  32.               .Cells(lRow, 6) = .Cells(Target.Row, 2) ' 客戶料號
  33.               .Cells(lRow, 7) = rStuff.Offset(, 1) ' 客戶PO
  34.               With .Cells(lRow, 8)
  35.                 .NumberFormat = "#,##0_ "
  36.                 .Value = rStuff.Offset(, 3) ' 出貨數
  37.               End With
  38.               vBalance = vBalance - rStuff.Offset(, 3)
  39.               rStuff.Offset(, 3) = 0
  40.               lRow = lRow + 1
  41.             Application.EnableEvents = True
  42.           Else
  43.             Application.EnableEvents = False
  44.               With .Cells(lRow, 5)
  45.                 .NumberFormat = "yyyy/m/d"
  46.                 .Value = .Parent.Cells(Target.Row, 1) ' 日期
  47.               End With
  48.               .Cells(lRow, 6) = .Cells(Target.Row, 2) ' 客戶料號
  49.               .Cells(lRow, 7) = rStuff.Offset(, 1) ' 客戶PO
  50.               With .Cells(lRow, 8)
  51.                 .NumberFormat = "#,##0_ "
  52.                 .Value = vBalance ' 出貨數
  53.               End With
  54.               rStuff.Offset(, 3) = rStuff.Offset(, 3) - vBalance
  55.             Application.EnableEvents = True
  56.             Exit Sub ' 尚未出貨數量等於0則跳出
  57.           End If
  58.         Loop ' While rStuff.Offset(, 3) <> 0
  59.       Loop Until rStuff = "" ' 直到最後一筆資料
  60.         If vBalance > 0 Then ' 輸入的出貨數未能全部分配完成
  61.   Application.EnableEvents = False ' 禁能會被觸發的動
  62.           vTemp = "輸入的出貨數未能全部分配完成, 出貨數由 " & rTemp & "調降至 "
  63.           rTemp = rTemp - vBalance
  64.           MsgBox vTemp & rTemp
  65.   Application.EnableEvents = True ' 致能(恢復)會被觸發的動作
  66.         End If
  67.     End If
  68.   End With
  69. End Sub
複製代碼
自動核消表-Ans5.zip (34.14 KB)
當初會加那一段也是考慮到若使用者不小心輸入過多的出貨量,
會造成無法還原回未輸入前的狀態,
結果因考慮不週迴圈條件未跟著調整,
反而衍申出其他的錯誤出現,
謝謝你的指正.

TOP

        靜思自在 : 好事要提得起，是非要放得下，成就別人即是成就自己。
返回列表 上一主題