麻辣家族討論版版 - Powered by Discuz! Board

標題: 依未結PO及要出貨數量,自動換算要出貨的PO及數量 [打印本頁]

作者: p6703 時間: 2012-9-21 10:25 標題: 依未結PO及要出貨數量,自動換算要出貨的PO及數量

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

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

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

希望可達到的功能：

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

[attach]12574[/attach]

作者: luhpro 時間: 2012-9-22 07:15

本帖最後由 luhpro 於 2012-9-22 07:16 編輯

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

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow%
Dim rStuff As Range
Dim bChecked As Boolean
Dim vPo, vBalance
Set vPo = Sheets("未結PO")
With Target.Parent '於 Sheets("出貨數量")
If .Cells(Target.Row, 2) <> "" And .Cells(Target.Row, 3) <> "" Then
iRow = IIf(.Cells(2, 5) = "", 2, .Cells(2, 5).End(xlDown).Row + 1)
vBalance = .Cells(Target.Row, 3)
Set rStuff = vPo.[A2] ' 於 Sheets("未結PO")
Do While .Cells(Target.Row, 2) <> rStuff ' 找到該客戶
Set rStuff = rStuff.Offset(1) ' 移到下一筆資料
Loop
Do
Do While rStuff.Offset(, 3) <> 0 ' 還有尚未出貨的資料
If vBalance > rStuff.Offset(, 3) Then ' 尚未出貨數量大於當下未交數
Application.EnableEvents = False
.Cells(iRow, 5) = .Cells(Target.Row, 1) ' 日期
.Cells(iRow, 6) = .Cells(Target.Row, 2) ' 客戶料號
.Cells(iRow, 7) = rStuff.Offset(, 1) ' 客戶PO
.Cells(iRow, 8) = rStuff.Offset(, 3) ' 出貨數
vBalance = vBalance - rStuff.Offset(, 3)
rStuff.Offset(, 3) = 0
iRow = iRow + 1
Application.EnableEvents = True
Else
Application.EnableEvents = False
.Cells(iRow, 5) = .Cells(Target.Row, 1) ' 日期
.Cells(iRow, 6) = .Cells(Target.Row, 2) ' 客戶料號
.Cells(iRow, 7) = rStuff.Offset(, 1) ' 客戶PO
.Cells(iRow, 8) = vBalance ' 出貨數
rStuff.Offset(, 3) = rStuff.Offset(, 3) - vBalance
Application.EnableEvents = True
Exit Sub ' 尚未出貨數量等於0則跳出
End If
Loop
Set rStuff = rStuff.Offset(1) ' 移到下一筆資料
Loop Until .Cells(Target.Row, 2) <> rStuff
End If
End With
End Sub

複製代碼

[attach]12587[/attach]

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

作者: p6703 時間: 2012-9-22 22:23

超感謝luhpro兄，原來巨集也可以這麼用，輸入符合的資料後自動秀出，真是讓小弟”大開眼界”
只是小弟所學有限，實不太清楚巨集是怎麼去判斷秀出符合PO及數量，此部份是否可請luhpro兄再講解清楚一點，再次感謝你^^

作者: p6703 時間: 2012-9-24 18:12

luhpro兄，小弟實際執行發現有以下問題，再煩請協助解惑，謝謝..

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

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

作者: p6703 時間: 2012-9-25 11:13

luhpro兄，小弟開始輸入資料，才第二筆就跑出以下錯誤，確定資料並未打錯，而"未結PO"當下未交數也沒錯，不知因何會如此

[attach]12600[/attach]

[attach]12601[/attach]

作者: luhpro 時間: 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")加上 :

Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Parent '於 Sheets("未結PO")
If .Cells(Target.Row, 1) <> "" And .Cells(Target.Row, 2) <> "" And .Cells(Target.Row, 3) <> "" Then
With .Cells(Target.Row, 3)
.NumberFormat = "#,##0_ "
.Offset(, 1).NumberFormat = "#,##0_ "
.Offset(, 1) = .Value
End With
End If
End With
End Sub

複製代碼

2. 這個處理上需要較多的動作,需要再想一下, 晚點再Post.

luhpro兄，小弟開始輸入資料，才第二筆就跑出以下錯誤，確定資料並未打錯，而"未結PO"當下未交數也沒錯，不 ...
p6703 發表於 2012-9-25 11:13

這可能是你資料列數超過 Integer 可處理範圍, 我將 iRow 改成 lRow 如下:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim rStuff As Range
Dim bChecked As Boolean
Dim vPo, vBalance
Set vPo = Sheets("未結PO")
With Target.Parent '於 Sheets("出貨數量")
If .Cells(Target.Row, 2) <> "" And .Cells(Target.Row, 3) <> "" Then
lRow = IIf(.Cells(2, 5) = "", 2, .Cells(2, 5).End(xlDown).Row + 1)
vBalance = .Cells(Target.Row, 3)
Set rStuff = vPo.[A2] ' 於 Sheets("未結PO")
Do While .Cells(Target.Row, 2) <> rStuff ' 找到該客戶
Set rStuff = rStuff.Offset(1) ' 移到下一筆資料
Loop
Do
Do While rStuff.Offset(, 3) <> 0 ' 還有尚未出貨的資料
If vBalance > rStuff.Offset(, 3) Then ' 尚未出貨數量大於當下未交數
Application.EnableEvents = False
.Cells(lRow, 5) = .Cells(Target.Row, 1) ' 日期
.Cells(lRow, 6) = .Cells(Target.Row, 2) ' 客戶料號
.Cells(lRow, 7) = rStuff.Offset(, 1) ' 客戶PO
.Cells(lRow, 8) = rStuff.Offset(, 3) ' 出貨數
vBalance = vBalance - rStuff.Offset(, 3)
rStuff.Offset(, 3) = 0
lRow = lRow + 1
Application.EnableEvents = True
Else
Application.EnableEvents = False
.Cells(lRow, 5) = .Cells(Target.Row, 1) ' 日期
.Cells(lRow, 6) = .Cells(Target.Row, 2) ' 客戶料號
.Cells(lRow, 7) = rStuff.Offset(, 1) ' 客戶PO
.Cells(lRow, 8) = vBalance ' 出貨數
rStuff.Offset(, 3) = rStuff.Offset(, 3) - vBalance
Application.EnableEvents = True
Exit Sub ' 尚未出貨數量等於0則跳出
End If
Loop
Set rStuff = rStuff.Offset(1) ' 移到下一筆資料
Loop Until .Cells(Target.Row, 2) <> rStuff
End If
End With
End Sub

複製代碼

作者: luhpro 時間: 2012-10-2 00:06

回復 5# p6703
因為程式落落長, 所以就不再貼上詳細程式的文字內容直接提供附檔囉.
[attach]12662[/attach]

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

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

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

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

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

作者: GBKEE 時間: 2012-10-2 10:16

回復 7# luhpro
Private Sub Worksheet_Change 是這 "未結PO" 工作表的觸動事件

Private Sub Worksheet_Change(ByVal Target As Range)
' With Target.Parent '於 Sheets("未結PO") '可不必有此With
' If .Cells(Target.Row, 1) <> "" And .Cells(Target.Row, 2) <> "" And .Cells(Target.Row, 3) <> "" Then
If Cells(Target.Row, 1) <> "" And Cells(Target.Row, 2) <> "" And Cells(Target.Row, 3) <> "" Then
With Cells(Target.Row, 3)
.NumberFormat = "#,##0_ "
.Offset(, 1).NumberFormat = "#,##0_ "
.Offset(, 1) = .Value
End With
End If
' End With
End Sub

複製代碼

作者: p6703 時間: 2012-10-2 11:05

感謝luhpro 兄費心的指導，小弟先實際套用看看，有問題再請問..^^。

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

作者: GBKEE 時間: 2012-10-2 11:26

回復 9# p6703
不是修改版,是說明 luhpro的程式可精簡

作者: luhpro 時間: 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

作者: p6703 時間: 2012-10-3 10:27

luhpro兄，小弟實際測試，除了感謝費時的修改，發現新輸入"未結PO"的資料無法再於"出貨數量"中秀出

例：小弟新增了35453 500,000，然後於出貨數中將原PO數都核消完後，就無法再產生新PO出貨明細了，請問是否小弟輸入方式有誤，再煩請協助看看，謝謝

另於 "輸入區" 中無資料區域的輸入方式與一般 Excel 輸入方式相同,
每輸完一筆資料就會直接做出貨配比,==>當下未交數是會自動秀出KEY IN的數量

作者: luhpro 時間: 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 的新增項目,
自然就無法正常參與出貨序列了.

修改後程式如下:
[attach]12689[/attach]

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

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

作者: p6703 時間: 2012-10-4 12:09

luhpro兄依提供的自動核消表-Ans3中在未結PO第34列直接增加一筆35451 500,000（此原未結已為0），然後再於出貨數量A~C欄位輸入日期及數量，但依舊跳出 "輸入的出貨數未能全部分配完成，出貨數由500000調降為0），請問是小弟操作順序有誤嗎???

作者: luhpro 時間: 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 ' 找到該客戶

[attach]12710[/attach]

作者: softsadwind 時間: 2012-10-6 17:07

回復 15# luhpro

我是路人~~~~~
我試著重新把已輸入的刪除
測試的結果如下圖.......大家看圖囉
[attach]12714[/attach]

作者: luhpro 時間: 2012-10-8 22:46

本帖最後由 luhpro 於 2012-10-8 22:47 編輯

回復 luhpro
我是路人~~~~~
我試著重新把已輸入的刪除
測試的結果如下圖.......大家看圖 ...
softsadwind 發表於 2012-10-6 17:07

修改程式如下:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim rStuff As Range, rTemp As Range
Dim bChecked As Boolean
Dim vPo, vBalance
With Target.Parent '於 Sheets("出貨數量")
Set vPo = Sheets("未結PO")
Set rTemp = .Cells(Target.Row, 3)
If .Cells(Target.Row, 2) <> "" And rTemp <> "" And Target.Column < 4 Then
lRow = .Cells(Rows.Count, 5).End(xlUp).Row + 1
vBalance = rTemp
Set rStuff = vPo.[A2] ' 於 Sheets("未結PO")
'Do Until .Cells(Target.Row, 2) = rStuff And rStuff.Offset(, 3) <> 0 ' 找到該客戶
' Set rStuff = rStuff.Offset(1) ' 移到下一筆資料
'Loop
Do ' Loop Until rStuff = ""
Do Until (.Cells(Target.Row, 2) = rStuff And rStuff.Offset(, 3) <> 0) Or rStuff = "" ' 找到該客戶或已沒資料
Set rStuff = rStuff.Offset(1) ' 移到下一筆資料
Loop
Do While rStuff.Offset(, 3) <> 0 ' 還有尚未出貨的資料
If vBalance > rStuff.Offset(, 3) Then ' 尚未出貨數量大於當下未交數
Application.EnableEvents = False
With .Cells(lRow, 5)
.NumberFormat = "yyyy/m/d"
.Value = .Parent.Cells(Target.Row, 1) ' 日期
End With
.Cells(lRow, 6) = .Cells(Target.Row, 2) ' 客戶料號
.Cells(lRow, 7) = rStuff.Offset(, 1) ' 客戶PO
With .Cells(lRow, 8)
.NumberFormat = "#,##0_ "
.Value = rStuff.Offset(, 3) ' 出貨數
End With
vBalance = vBalance - rStuff.Offset(, 3)
rStuff.Offset(, 3) = 0
lRow = lRow + 1
Application.EnableEvents = True
Else
Application.EnableEvents = False
With .Cells(lRow, 5)
.NumberFormat = "yyyy/m/d"
.Value = .Parent.Cells(Target.Row, 1) ' 日期
End With
.Cells(lRow, 6) = .Cells(Target.Row, 2) ' 客戶料號
.Cells(lRow, 7) = rStuff.Offset(, 1) ' 客戶PO
With .Cells(lRow, 8)
.NumberFormat = "#,##0_ "
.Value = vBalance ' 出貨數
End With
rStuff.Offset(, 3) = rStuff.Offset(, 3) - vBalance
Application.EnableEvents = True
Exit Sub ' 尚未出貨數量等於0則跳出
End If
Loop ' While rStuff.Offset(, 3) <> 0
Loop Until rStuff = "" ' 直到最後一筆資料
If vBalance > 0 Then ' 輸入的出貨數未能全部分配完成
Application.EnableEvents = False ' 禁能會被觸發的動
vTemp = "輸入的出貨數未能全部分配完成, 出貨數由 " & rTemp & "調降至 "
rTemp = rTemp - vBalance
MsgBox vTemp & rTemp
Application.EnableEvents = True ' 致能(恢復)會被觸發的動作
End If
End If
End With
End Sub

複製代碼

[attach]12725[/attach]
當初會加那一段也是考慮到若使用者不小心輸入過多的出貨量,
會造成無法還原回未輸入前的狀態,
結果因考慮不週迴圈條件未跟著調整,
反而衍申出其他的錯誤出現,
謝謝你的指正.

作者: softsadwind 時間: 2012-10-9 00:30

回復 17# luhpro

我這幾天很努力的看你寫的程式
但是還是頭暈暈跟不上.......
感謝你願意更新...讓我有學習的機會

作者: p6703 時間: 2012-10-12 16:13

感謝luhpro 兄多次修改，小弟現使用上暫無問題，在此再次誠心感謝，真的幫了小弟的大忙^^

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)