12
返回列表 上一主題 發帖

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

p6703

帖子
134 
主題
27 
精華
0 
積分
168 
點名
0  
作業系統
WINXP 
軟體版本
EXCEL 2003 
閱讀權限
20 
性別
男 
來自
雲林 
註冊時間
2010-10-6 
最後登錄
2018-5-13 

論壇贊助勳章
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2012-9-21 10:25 | 只看該作者

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

此問題原發於一般區，但如以巨集是否也可自動套取，故再次將同問題發於此區中，請各位大大指教，先謝謝各位...

如附件，小弟有未結PO及出貨數量二工作表

未結PO：是指與客戶未結的總明細
出貨數量：列出當天該客戶料號出貨的總數

希望可達到的功能：

1.於未結PO工作表中的D欄，可依出貨數量中的明細，自動換算出現各PO未結數量
2.出貨數量工作表中的E~H欄位，可依A~Ｃ欄位出貨數自動換算對應的PO

Book1.rar (3.42 KB)
收藏 分享

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
16  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
2#
發表於 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

p6703

帖子
134 
主題
27 
精華
0 
積分
168 
點名
0  
作業系統
WINXP 
軟體版本
EXCEL 2003 
閱讀權限
20 
性別
男 
來自
雲林 
註冊時間
2010-10-6 
最後登錄
2018-5-13 

論壇贊助勳章
3#
發表於 2012-9-22 22:23 | 只看該作者
超感謝luhpro兄，原來巨集也可以這麼用，輸入符合的資料後自動秀出，真是讓小弟”大開眼界”
只是小弟所學有限，實不太清楚巨集是怎麼去判斷秀出符合PO及數量，此部份是否可請luhpro兄再講解清楚一點，再次感謝你^^

TOP

p6703

帖子
134 
主題
27 
精華
0 
積分
168 
點名
0  
作業系統
WINXP 
軟體版本
EXCEL 2003 
閱讀權限
20 
性別
男 
來自
雲林 
註冊時間
2010-10-6 
最後登錄
2018-5-13 

論壇贊助勳章
4#
發表於 2012-9-24 18:12 | 只看該作者
luhpro兄，小弟實際執行發現有以下問題，再煩請協助解惑，謝謝..

1.新增的筆數資料不會捉到
因PO會陸續增加，小弟將新PO資料KEY IN ＂未結PO＂工作表中，然後再於出貨數量KEY 上出貨的數據，卻無法自動跑出新PO

2.出貨數量一旦KEY IN錯誤，造成未結PO的數據跟著錯誤
即使將該錯誤資料重新輸入，但當下未交數就已扣除無法回推到正確數量，因此資料將累積輸入，如因一筆輸錯就造成無法回推，那很難實際核對到真正未結PO

TOP

p6703

帖子
134 
主題
27 
精華
0 
積分
168 
點名
0  
作業系統
WINXP 
軟體版本
EXCEL 2003 
閱讀權限
20 
性別
男 
來自
雲林 
註冊時間
2010-10-6 
最後登錄
2018-5-13 

論壇贊助勳章
5#
發表於 2012-9-25 11:13 | 只看該作者
luhpro兄，小弟開始輸入資料，才第二筆就跑出以下錯誤，確定資料並未打錯，而"未結PO"當下未交數也沒錯，不知因何會如此



溢位.jpg

TOP

luhpro

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

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

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


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

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

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

TOP

GBKEE

帖子
5923 
主題
13 
精華
1 
積分
5986 
點名
0  
作業系統
win10 
軟體版本
Office 2010 
閱讀權限
150 
性別
男 
來自
台灣基隆 
註冊時間
2010-5-1 
最後登錄
2022-1-23 

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
8#
發表於 2012-10-2 10:16 | 只看該作者
回復 7# luhpro
Private Sub Worksheet_Change 是這 "未結PO" 工作表的觸動事件
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. '  With Target.Parent '於 Sheets("未結PO")   '可不必有此With
  3.    ' If .Cells(Target.Row, 1) <> "" And .Cells(Target.Row, 2) <> "" And .Cells(Target.Row, 3) <> "" Then
  4.     If Cells(Target.Row, 1) <> "" And Cells(Target.Row, 2) <> "" And Cells(Target.Row, 3) <> "" Then
  5.       With Cells(Target.Row, 3)
  6.         .NumberFormat = "#,##0_ "
  7.         .Offset(, 1).NumberFormat = "#,##0_ "
  8.         .Offset(, 1) = .Value
  9.       End With
  10.     End If
  11. ' End With
  12. End Sub
複製代碼

TOP

p6703

帖子
134 
主題
27 
精華
0 
積分
168 
點名
0  
作業系統
WINXP 
軟體版本
EXCEL 2003 
閱讀權限
20 
性別
男 
來自
雲林 
註冊時間
2010-10-6 
最後登錄
2018-5-13 

論壇贊助勳章
9#
發表於 2012-10-2 11:05 | 只看該作者
感謝luhpro 兄費心的指導，小弟先實際套用看看，有問題再請問..^^。

GBKEE版主的程式碼是修改版的嗎???

TOP

GBKEE

帖子
5923 
主題
13 
精華
1 
積分
5986 
點名
0  
作業系統
win10 
軟體版本
Office 2010 
閱讀權限
150 
性別
男 
來自
台灣基隆 
註冊時間
2010-5-1 
最後登錄
2022-1-23 

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
10#
發表於 2012-10-2 11:26 | 只看該作者
回復 9# p6703
不是修改版,是說明 luhpro的程式可精簡

TOP

        靜思自在 : 有智慧才能分辨善惡邪正；有謙虛才能建立美滿人生。
12
返回列表 上一主題