Board logo

標題: [發問] 對應日期貼盤點資料 [打印本頁]

作者: PJChen    時間: 2021-7-4 13:32     標題: 對應日期貼盤點資料

本帖最後由 PJChen 於 2021-7-4 13:36 編輯

大大們好,
    With Sh
    Sh.Activate
        For j = 6 To xrow Step 2
            .Range("X" & j + 1 + 1).Resize(1, 16).Copy
            xW.Sheets("盤點").Range("BO" & 1 + j).Resize(1, 16).PasteSpecial xlPasteValues
        Next
    End With

盤點工作表從A欄的各客戶檔案把相應的盤點資料,copy過來
E4是指定日期
A欄是客戶名
當DI欄= D+0則從客戶檔案的數字工作表找E4-1的工作表
ex:E4=7/3,則找"2"工作表
將x:am的盤點資料,有庫存的數字,對應日期相符,貼到盤點工作表的BH:CF的欄位中

來源各客戶的檔案格式不會完全相同,但都是類似的,只以一定作範本,
盤點工作表中有很多的公式,所以貼上的資料不能干擾其他儲存格,
請問(紅字)對應日期貼上盤點數值的這段程式該怎麼寫?  [attach]33502[/attach]
  1. Sub copy_蜜蜂盤點()
  2. Dim PH$, FN$, W As Workbook, xW As Workbook, xD As Worksheet, xS As Worksheet, Sh As Worksheet, i As String, rng As Range
  3. Set xD = ThisWorkbook.Sheets("VBA")  '程式來源
  4. k = xD.[V1] - 1 '取前一日
  5. i = Format(k, "D")
  6. y = Format(k, "yyyy")
  7. m = Format(k, "m")
  8. '---------------------
  9. Set xW = Workbooks("多客戶盤點表")
  10. PH = xD.[BB1]
  11. '---------------------
  12. FN = Dir(PH & "*蜜蜂*" & y & "*" & m & ".xlsx")
  13. Do While FN <> ""
  14. On Error Resume Next: Set W = Workbooks(FN): On Error GoTo 0
  15. If W Is Nothing Then Set W = Workbooks.Open(PH & FN)
  16. Set Sh = W.Sheets(i)
  17. With Sh
  18.     xrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row + 5
  19. End With
  20.      xW.Sheets("盤點").Range("M6:M" & xrow) = Sh.Range("T7:T" & xrow).Value '來源 前日結餘   
  21. With Sh
  22.     Sh.Activate
  23.         For j = 6 To xrow Step 2
  24.             .Range("X" & j + 1 + 1).Resize(1, 16).Copy
  25.             xW.Sheets("盤點").Range("BO" & 1 + j).Resize(1, 16).PasteSpecial xlPasteValues
  26.         Next
  27.     End With
  28.    
  29. FN = Dir
  30. Loop
  31. End Sub
複製代碼

作者: PJChen    時間: 2021-7-5 16:36

回復 1# PJChen

多客戶盤點表.xlsx BH:GF欄日期的比對要以到期日(雙號列)為準,列5是生產日,不要作比對...
作者: 軒云熊    時間: 2021-7-9 23:14

本帖最後由 軒云熊 於 2021-7-9 23:21 編輯

回復 2# PJChen

請問 PJChen大大  多客戶盤點表 的 DI欄 的 0 跟 1 是怎麼來的? 可以說明一下嗎? 感謝 我是說 0 跟 1 是甚麼用意?
是依據甚麼 為甚麼要日期減1 是因為 4號沒有資料的關西嗎? 我不太明白 >"<
作者: 軒云熊    時間: 2021-7-10 01:29

回復 2# PJChen

有空幫我試試看是不是類似這樣的結果 我只是猜測
[attach]33556[/attach]
作者: PJChen    時間: 2021-7-12 20:40

本帖最後由 PJChen 於 2021-7-12 20:41 編輯

回復 4# 軒云熊

多客戶盤點表 是目的資料檔,接收各客戶的盤點資料
DI欄 的 0 跟 1 是自訂的,
例如今天是7/12,DI欄 出現的是0,則來自客戶的盤點資料則要貼12-1=11工作表的盤點資料,
DI欄 出現的是1,則來自客戶的盤點資料則要貼12-0=12工作表的盤點資料,
這個是固定的規則模式,完全無變化的,是公司作業的規則而已!
所有的表格都是測試用,可以任意填入資料,用來做程式測試,
今天是7/12,我在來源檔的11工作表,任意填入些資料,測試程式,發現完全無貼上的動作

來源的客戶盤點格式分2種類型,一種是2格合併(目的工作表是貼在"盤點"),一種是3格合併(目的工作表是貼在"stock"),
來源的客戶盤點資料有很多個,所以做成的程式,希望能自行指定來源的盤點區(盤點區不一定在哪個儲存格),就能貼上資料
現將來源的客戶盤點檔,各附上一個測試檔
[attach]33597[/attach]
作者: 軒云熊    時間: 2021-7-13 16:28

本帖最後由 軒云熊 於 2021-7-13 16:33 編輯

回復 5# PJChen

stock 跟 萬達庫存表 2021.07 日期對不到  好像怪怪的 妳再確認一下

stock 直有 7/7  而 萬達庫存表 是7/6~7/11 還是 只要 7/7 號?  不太明白 可以說清楚一點嗎 感謝 ^^"
作者: PJChen    時間: 2021-7-13 17:52

回復 6# 軒云熊
stock 跟 萬達庫存表的測試檔,日期都是測試的,所以改一下可以測試就好了 [attach]33612[/attach]
----------萬達庫存表 盤點資料
(低脂)U8:AF8,對應stock BH7:CF7
(原味)U11:AF11,對應stock BH10:CF10
有相同日期的盤點數,就貼到stock同相日期的盤點格內

----------蜜蜂倉 盤點資料
V7:AK7,V9:AK9,V11:AK11同品名&同日期對應
多客戶盤點表.sh(盤點)BH6:CF6,BH8:CF8,BH10:CF10
作者: 軒云熊    時間: 2021-7-14 18:22

回復 7# PJChen
有空幫我試試看 感謝

[attach]33636[/attach]
作者: PJChen    時間: 2021-7-14 21:19

回復 8# 軒云熊
熊大好,

測試結果,Stock的日期沒有連續時,貼上的盤點資料會不正確,測試結果留在工作表中,
我有很多檔案要用到這個2個程式,可否幫忙註解程式?
[attach]33638[/attach]
作者: 軒云熊    時間: 2021-7-15 00:21

回復 9# PJChen

有空再幫我試試看  可以再多幾個要比對的檔案嗎? 感謝
因為品名 我沒有判斷

[attach]33639[/attach]
作者: PJChen    時間: 2021-7-15 21:45

回復 10# 軒云熊

再附上2個測試檔 [attach]33652[/attach]
程式我多測試些再回覆
請問程式備註,能否再詳細些?
作者: PJChen    時間: 2021-7-16 00:19

回復 10# 軒云熊
熊大,
請幫忙研究一下,
a) 目的檔有二種固定格式,且key-in的欄位範圍相同,只有合併儲存格分2列&3列
b) 而來源檔雖然盤點範圍不同,但格式大致相同,只有列數的不同,也分為二種格式
1) 盤點區在(盤差)(預設天數)之間
2) 盤點區在(結餘合計)(盤差)之間
有沒有可能程式可以只改檔名,就可以適用於從來源貼到目的檔?
(最多就分成2個程式,分別是貼到盤點、stock)
因為客戶會有增加的可能性,品名也是,
希望能夠在增加客戶&品名時,只要在工作表往下增加,就可以使用,
不要再去修改程式!
另外還想請問,檔名因為都很長,我想改為開檔時用ex: *永遠*,這樣的方法,
我只知道這種寫法,不知還有其他方式嗎?
   Do While File <> ""
        With Workbooks.Open(Path & File)
------
   File = Dir
Loop
[attach]33653[/attach]
作者: 軒云熊    時間: 2021-7-16 04:53

本帖最後由 軒云熊 於 2021-7-16 05:02 編輯

回復 11# PJChen
stock 工作表 我沒有改 因為我這直有一種格式 如果還有 請再多給幾個  只要把要貼的檔案都放在同一個資料匣就可以了不用改檔名
有空再幫我試試看 這樣可不可以 註解有加了一些  如果不夠清楚 可以再跟我說是哪一段 感謝
因為這寫法不是很好 看起來很亂 而且迴圈也太多了 如果檔案多的話會很慢 看看有沒有大大願意幫忙  我也可以順便學習 ^^"
感覺我的邏輯有問題 >"<
[attach]33654[/attach]
作者: PJChen    時間: 2021-7-19 21:51

回復 13# 軒云熊
熊大好,
1) stock工作表,不能改為Sheets(數字),實際使用檔含有其他工作表,這樣會出錯
2) stock的來源檔,只要品名順序不同,就無法貼上資料,程式就中斷了
3) Workbooks("多客戶盤點表.xlsx").Sheets("盤點")也是相同情形,不能改為Sheets(數字)
4) 公司的存放區,有很多檔,不只是這幾個檔案,且有類似檔名,所以用Dir(U & "*.xlsx")也會出錯
     上次詢問:檔名因為都很長,想改為開檔時用ex: *永遠*,這樣的方法,不知可否?
5) 二個程式都需要有比對品名的功能才行,不然一旦品名排列不同,就會出錯了
[attach]33689[/attach]
作者: 軒云熊    時間: 2021-7-20 02:03

本帖最後由 軒云熊 於 2021-7-20 02:05 編輯

回復 14# PJChen
請問 有日期部分的儲存格 位置是不是固定的? 還是每一個活頁簿都不一樣? 列如 萬達庫存表 有日期的部分欄位格式
因為我看其它的活頁簿 格式都是不固定的 有的是從v7 開始 有的不是  是因為這是測試檔案的關係 還是原始檔案也是這樣?
作者: n7822123    時間: 2021-7-20 10:24

本帖最後由 n7822123 於 2021-7-20 10:32 編輯

回復 13# 軒云熊
回復 14# PJChen

有時候不是我不想幫,是我連需求都沒看懂......

感覺樓主的問題都是實務上的問題,對不熟你們公司表格填寫、運作方式的人來說,有點複雜

誠心建議,樓主可以試著把複雜的問題,簡單化之後再發問,一定會比較多人幫忙

因為看你之前的發問,推測出你有一些些的VBA基礎的 (我記得你有問過Ubound之類的)

程式可以像拼圖一樣,可拆分成很多小部分的

假如你的需求需要程式寫N個步驟,但你只有某步驟卡住不會,其他功能你都可以自己寫出來

只要把你不會的部分,單獨拿出來發問即可,之後你就可以組合出你要的完整功能

小到新增工作表、複製1列資料,都可以當作步驟

我們在解需求的時候,也是會先想整體操作步驟(程式主架構)

再一個個完成一開始想的步驟,你需要做的只是,把你原本"手動"操作的部分

分步驟一個個列出來,再試著寫這些步驟,寫不出來的部分,就發問

以下舉例說明~


假如你的需求需要程式做10個步驟,但你只有步驟6卡住不會

單獨把步驟6拿出來發問,問到答案之後,自己組合出你要的功能

當然測試檔要先自己完成步驟1~5(手動or寫VBA),再附到論壇上發問


Sub 完整功能主程序()
  Call 前面會的副程序   '步驟1~5
  Call 步驟6
  Call 後面會的副程序   '步驟7~10
End Sub

Sub 步驟6()
'須發問,不會做的部分
End Sub

Sub 前面會的副程序()
步驟1
步驟2
步驟3
步驟4
步驟5
End Sub

Sub 後面會的副程序()
步驟7
步驟8
步驟9
步驟10
End Sub

作者: 軒云熊    時間: 2021-7-20 12:06

本帖最後由 軒云熊 於 2021-7-20 12:08 編輯

回復 16# n7822123

n7822123前輩好  
樓主的需求是  
自動開啟指定的活頁簿檔案   多客戶盤點表
分別有2個工作表 2種格式  
日期欄位 BH~CF  的範圍之間
"盤點" 工作表 (STEP2)ROW跳2格
"stock" 工作表 (STEP3)ROW跳3格

將E4的日期與 DI欄位的日期做比對
列如:   DI6 =  D+0  E4 就扣一天
            DI6 =  D+1 則  保持不變
在某些位置有穿插帶公式的儲存格 保持不變
要比對的項目 有 客戶 品名 還有日期欄位 BH~CF  的範圍之間
-------------------
開啟要比對的檔案 
這裡也是用指定檔名的方式開啟
列如:多客戶盤點表 FORMAT(E4,苠疿苤^與 萬達庫存表 相對應的日期工作表SHEETS(X)
-------------------
萬達庫存表 是對應 多客戶盤點表 的 "stock" 工作表 (STEP3)ROW跳3格
因為有2種格式分別互相對應  
找到相對應的日期就把數值貼上(多客戶盤點表)
我現在問題出在要比對的儲存格位置沒有固定
列如:萬達庫存表
品名欄位是合併狀態
右邊 U∼AF位置不固定 不一定是U∼AF也有可能是從V開始或著其它欄位開始
目前發現 有郇L點虷r樣的位置 可以找出範圍 但不知道如何做
請問前輩 要怎麼把相對應的 品名 與 日期 還有日期下方的數值 正確的放到陣列 方便做比對
如果直接全部塞到陣列不好做比對 因為工作表的格式內容太多而且會變更複雜
作者: 軒云熊    時間: 2021-7-20 13:50

回復 16# n7822123

抱歉前輩 忘記附上檔案
[attach]33693[/attach]
作者: n7822123    時間: 2021-7-20 15:16

本帖最後由 n7822123 於 2021-7-20 15:21 編輯

回復 18# 軒云熊

結合你的說明,以及前面討論串,終於看懂了
其實只是A檔A表A格式 搬移資料到 B檔B表B格式
難在不固定表格,要找需要的資料範圍


因為有2種格式分別互相對應  
找到相對應的日期就把數值貼上(多客戶盤點表)

來源表盤差下面的 XX箱,可以用合併儲存格"列數"(Columns.Count),判斷是2 Row 還是 3 Row

我現在問題出在要比對的儲存格位置沒有固定
列如:萬達庫存表
品名欄位是合併狀態

你說的是"E欄"的品名吧
合併儲存格,只有第一格有資料,其餘都是空白
利用此邏輯判斷好,是可以處理的
但我發現 E欄 BG欄 都有品名,還互相衝突ˊˋ


[attach]33695[/attach]

右邊 U∼AF位置不固定 不一定是U∼AF也有可能是從V開始或著其它欄位開始
目前發現 有郇L點虷r樣的位置 可以找出範圍 但不知道如何做

看了各表格,確實只能用"盤點"來確定範圍
本來想用比較少用的字樣"盤差"來找範圍
但發現有的日期在盤差後面,有的在前面......(這格式不科學阿),所以還是盤點吧
針對這種不固定範圍的表格,須要先找一個 "不重複、唯一" 的關鍵字來"定位"
看了下來源表格,盤點在同一張表格也是唯一的,所以可以用
利用Range物件的"Find"方法可以找關鍵字的欄位


請問前輩 要怎麼把相對應的 品名 與 日期 還有日期下方的數值 正確的放到陣列 方便做比對
如果直接全部塞到陣列不好做比對 因為工作表的格式內容太多而且會變更複雜

上面那一步確認範圍之後,就可以塞進陣列了
結合合併儲存格的邏輯,只有第一格有資料
所以起始欄可找"盤點"字樣的欄位,終點欄位可以利用該合併儲存格的"欄數"(Columns.Count)來計算
起點列看起來蠻固定的,都從第7列開始,或者找"盤點"列+2
終點列看起來可以用A欄(保存天數)的末端資料來判斷


好啦,你的問題我都解答完畢了
我還沒要開始寫
你先挑戰看看吧~

作者: 軒云熊    時間: 2021-7-20 19:41

本帖最後由 軒云熊 於 2021-7-20 19:55 編輯

回復 14# PJChen

感謝 n7822123前輩提點 現在已經可以放到陣列了 
合併儲存格的"欄數"(Columns.Count)不知如何計算 >苤捸@
我是用 End(2).Column - 1 的方式 不知道會不會有問題 目前看起來是還可以
但迴圈卻變得更多了呵呵..先不管了 先請PJChen大大幫我試試看 sheets("stock" )部分有沒有問題 感謝

[attach]33700[/attach]
作者: n7822123    時間: 2021-7-20 20:09

本帖最後由 n7822123 於 2021-7-20 20:14 編輯

回復 20# 軒云熊

感謝 n7822123前輩提點 現在已經可以放到陣列了 
合併儲存格的"欄數"(Columns.Count)不知如何計算 >苤捸@

參考看看

Set Rg = .Cells.Find("盤點", , , xlWhole)  '找值="盤點"的表格
If Not Rg Is Nothing Then
    C0 = Rg.Column                                          '來源表起始欄
    C_Cnt = Rg.MergeArea.Columns.Count   '資料欄數
End If


他的表格其實本身有Bug,如下圖250g前面 有的有空白,有的沒有

而且品名有很多只差別一個數字(Ex:鮮乳1~N)

所以品名也不能模糊比對,若樓主回饋有問題,先檢查資料吧~

這要我來寫,可能要寫一堆防呆程式,不然哪裡有問題都抓不到


[attach]33703[/attach]
作者: PJChen    時間: 2021-7-20 22:18

回復 20# 軒云熊
這個程式檔,只有一個stock的程式,所以就先測試這個
(低脂) 250g無法貼上盤點資料
作者: n7822123    時間: 2021-7-20 23:06

本帖最後由 n7822123 於 2021-7-20 23:14 編輯

回復 20# 軒云熊
回復 22# PJChen

我是寫好了....迴圈最多就2層,但應該也沒那麼好懂

註解已經寫好寫滿了,一半都是註解~

主要是格式不太有規律

不然小小的資料搬移程式不用寫到那麼多行...

感覺寫得有點冗長.....應該可以再簡化一些

不過先請樓主確認功能面吧~


[attach]33706[/attach]
作者: 軒云熊    時間: 2021-7-20 23:30

本帖最後由 軒云熊 於 2021-7-20 23:35 編輯

回復 22# PJChen

謝謝n7822123前輩 先下載來研究一下 :P

回 PJChen大大 (低脂) 250g 的日期改一下 就可以了 因為 日期對不到所以沒有貼上

萬達庫存表 2021.07 日期 與 多客戶盤點表 stock 日期大部分都不相同 直有 (原味)250g  1/5        1/6        1/7 日期是正確的
作者: n7822123    時間: 2021-7-21 00:10

回復 24# 軒云熊

沒錯,我在測試時也發現日期有2021 與 2022 兩種(有的保值期1年,有的才45天)

所以我把很多資料都改過了,為了測試

如果有看不懂的地方,再發問吧~用了一些技巧,

如果不用的話迴圈會變多,不容易Debug

作者: 軒云熊    時間: 2021-7-21 14:58

回復 22# PJChen

有空再幫我試試看 有沒有問題  我是分開寫 而且迴圈也比較多  
測試的時候 日期 可能要修改一下 有些檔案日期對應不到

n7822123前輩的寫法我還要再研究  慢慢吸收 ^^"  
感謝  n7822123前輩 不然會卡更久...呵呵..
[attach]33712[/attach]
作者: PJChen    時間: 2021-7-21 22:16

本帖最後由 PJChen 於 2021-7-21 22:20 編輯

回復 26# 軒云熊

熊大好,
舊的測試檔,都沒什麼問題,我會再用正式的檔案測看看
另,我新增了一個測試檔,發現無法運作,請幫我看一下
[attach]33715[/attach]
作者: PJChen    時間: 2021-7-21 22:19

回復 23# n7822123
n大好,
我下載附件測試,但程式都沒有反應,沒貼上任何資料,不知是有什麼特別測試方法嗎?
作者: n7822123    時間: 2021-7-21 22:52

本帖最後由 n7822123 於 2021-7-21 22:54 編輯

回復 28# PJChen


我剛剛下載了論壇的檔案,測過沒問題呀

只開巨集檔,按下面按鈕就跑完了

你可以把多客戶盤點表,巨集要填的內容都刪除

執行後看看,因為給的附件應該是執行過的了

如果你都沒改程式的話,預設都放在同一個資料夾下

[attach]33716[/attach]
[attach]33717[/attach]
作者: 軒云熊    時間: 2021-7-22 01:42

回復 27# PJChen

因為 工作表的Format(SP(2), "D") 與 x 對不到  改用文字格式比對看看
建議用 n7822123 前輩寫的會比較好 結果是一樣的 效率較高


[attach]33718[/attach]
作者: PJChen    時間: 2021-7-22 19:54

回復 21# n7822123
抱歉,這個品名中間沒有空格,是我打錯了
作者: PJChen    時間: 2021-7-22 20:08

回復 29# n7822123
大大,不好意思
執行後還是連一筆資料都沒貼上
另外,    iFile$ = Dir(iPath & "\*" & Keys(1) & "*.xls*")   '確認是否有此來源檔
實際的路徑有很多同事的檔案&類以的檔名,
如何在程式中排除不想比對的檔案?
例如:以下二個檔,不要加入比對中的話,要怎麼寫入程式中排除它?
佳佳(湖口廠)庫存表.xlsx
廠缺回饋.xlsx
[attach]33722[/attach]
作者: n7822123    時間: 2021-7-23 12:16

本帖最後由 n7822123 於 2021-7-23 12:19 編輯

回復 32# PJChen

執行後還是連一筆資料都沒貼上

你先別用你的測試檔,你先用我上次上傳裡面的測試檔看看

如果還是一樣,那可能是環境問題,如果我的測試檔沒問題

那可能是你的儲存格資料沒有相對應、或者你又有其他檔案格式

我上次就發現你的"品名"  很有問題

還有說明盡量用"圖片",不然別人很難懂你在表達什麼





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