12
返回列表 上一主題 發帖

[發問] 搜尋符合條件的訂單號碼

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2017-7-4 02:32 | 顯示全部帖子

[發問] 搜尋符合條件的訂單號碼

大大好,

在"除外工作表"k1有一個指定的訂單號碼(它會依需求而變動號碼)為依據,用來搜尋BOM,請購,領料...等工作表中符合此訂單號碼的就將全部料號依序排列出,請問什麼函數可以達成這個要求?
1) 其中BOM工作表F欄料號有時資料會空白,所以是有的話列出在除外工作表F欄位,沒有的話就空白,料號不能重複出現,並在F1標示總共有幾筆資料領料工作表(加總筆數要來自BOM工作表)
2) 請購,領料2個工作表的資料包含各種訂單號碼,所以料號是不會依訂單順序出現的
3) 請購工作表D欄符合訂單號碼的話,就將料號列在除外工作表G欄位,料號不能重複出現,並在g1標示總共有幾筆資料(加總筆數要來自請購工作表)
4) 領料工作表D欄符合訂單號碼的話,就將料號列在除外工作表J欄位,料號不能重複出現,並在j1標示總共有幾筆資料(加總筆數要來自領料工作表)
註:原本工作表中資料幾千筆,因為檔案太大刪除很多資料,所以可以看到的訂單資料沒有很多.
料號.rar (43.68 KB)
收藏 分享

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
2#
發表於 2017-7-10 00:52 | 顯示全部帖子
我把問題再寫得明確一點,
1        當"VBA報表指令.xlsm"H2儲存格設定值是M2時
2        在來源資料檔"庫存資料表.xlsx"的D欄,找尋第一個出現的M2(在D11欄位)
3        copy來源資料檔"庫存資料表.xlsx"的第11列,從A11:AA的資料最底端 (這不能打上一個實際的儲存格範圍,因為資料會變動)
4        貼到目的檔"庫存.xlsx"的A11(相對位置是D欄第一筆出現的M2位置:在第11列)

請問有人會寫這樣的VBA嗎？   依變數搜尋資料.rar (298.83 KB)

TOP

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
3#
發表於 2017-7-10 18:14 | 顯示全部帖子
回復 3# Hsieh

大大好,

我抓的報表日期區間可能就是當月份的,Excel檔中的資料會累積比較多資料,所以我在VBA報表指令.xlsm H2中指定一個單號名稱,而一個單號可能有很多筆,所以我要指定它可以從找到的第一筆開始貼上,這樣就不會蓋掉我需要的資料,同時也可以更新我要的資料.

只是我手上正好有這個資料,所以就偷懶用這個檔來詢問,不過觀念是相同的.

TOP

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
4#
發表於 2017-7-11 23:44 | 顯示全部帖子
回復 5# Hsieh

大大,

我測試了幾次,發現一些問題,要再麻煩您修改下.

1. 在測試的時候,我故意把"庫存.xlsx"的資料刪除,只保留至1000列.
2. "庫存資料表"共1059列(都沒有刪除), 當我把"VBA報表指令.xlsm" H2的變數改為M1時,它應該要從"庫存資料表"的A1:AA1059複製到"庫存.xlsx"的A1:AA1059貼滿,但它只詢問有9列新資料是否要更新.
3. 當我把"庫存.xlsx"的資料A欄資料保留10列,其餘A11以後為空白時，它也無法正常更新資料

P.S. 大大寫的程式因為不是巨集式的,我有看沒有懂,您可否幫我註解再更詳細些，因為這個程式,我會應用到很多資料上，詳細的註解有助於我日後的小修改.  
附上我測試的檔案 依變數搜尋資料2.rar (298.47 KB)

先謝謝了.

TOP

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
5#
發表於 2017-7-14 23:38 | 顯示全部帖子
回復 7# Hsieh

大大,
1) 經測試,貼資料的時候,它會把AA欄的資料貼到A欄,然後其他資料向右移一個欄位....請問要如何修正?
2) 因為這個程式,我還會使用在其他的文件上，books = Array("庫存資料表.xlsx", "庫存.xlsx"),是否檔名不同時,我只要改不同顏色的檔名即可？其他檔名如程式中的Sheets(1)我不用動它,是嗎？
3) 當我的檔案存放路徑不同時,我需要修改什麼地方嗎？
4) 但每份報表的貼上位置不同,如果我要自己修改，以VBA報表指令.xlsm的VBA指令.sheet H2儲存格為搜尋準則去搜尋目的檔C欄,但是貼上要在B欄,我要怎麼修改?

不好意思,麻煩你了...

TOP

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
6#
發表於 2017-7-15 23:55 | 顯示全部帖子
回復 7# Hsieh
大大,
我修改了些小地方,現在貼上時正常了.
  1. Sub copy_all()
  2. Dim ws() '已經開啟視窗
  3. books = Array("庫存資料表.xlsx", "庫存.xlsx") '欲開啟檔案
  4. mypath = ThisWorkbook.Path '存放檔案資料夾
  5. For Each W In Windows '已經開啟視窗
  6.    ReDim Preserve ws(s)
  7.    ws(s) = W.Caption
  8.    s = s + 1
  9. Next
  10. For Each b In books '測試檔案是否開啟
  11.    If UBound(Filter(ws, b)) = -1 Then Workbooks.Open (mypath & "\" & b) '檔案未開啟則開啟
  12. Next
  13. x = ThisWorkbook.Sheets(1).[H2] '準則
  14. With Workbooks(books(0)).Sheets(1) '庫存資料表.xlsx
  15.     Set a = .Columns("D").Find(x, lookat:=xlWhole) '找準則位置
  16.     If a Is Nothing Then MsgBox "找不到準則位置": End
  17.     Set Rng = .Range(a.Offset(, -3), a.End(xlDown).Offset(, 23)) 'B:AA欄資料
  18.     'MsgBox Rng.Address
  19.     With Workbooks(books(1)).Sheets(1) '庫存.xlsx
  20.        Set a = .Columns("D").Find(x, lookat:=xlWhole) '找準則位置
  21.        If a Is Nothing Then Set a = .Cells(.Rows.Count, 4).End(xlUp).Offset(1) '原資料不存在準則資料
  22.        .Range("A" & a.Row & ":AA" & .Rows.Count).ClearContents
  23.           a.Offset(, -3).Resize(Rng.Rows.Count, 27).Value = Rng.Value '寫入新資料
  24.           'a.Offset(, -3).Resize(Rng.Rows.Count, 1) = Rng.Columns(5).Value '寫入A欄編號
  25.           MsgBox "資料已更新"
  26.     End With
  27. End With
  28. End Sub
複製代碼

TOP

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
7#
發表於 2017-7-16 01:06 | 顯示全部帖子
回復 7# Hsieh

我自己修改了程式但沒有貼的動作,請問
Row.Count要如何數？Offset(1)又代表什麼？
還有第二行的程式要怎麼解讀？
  1. If a Is Nothing Then Set a = .Cells(.Rows.Count, 4).End(xlUp).Offset(1) '原資料不存在準則資料
  2.        .Range("A" & a.Row & ":AA" & .Rows.Count).ClearContents
複製代碼

TOP

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
8#
發表於 2017-7-16 14:54 | 顯示全部帖子
回復 7# Hsieh

大大,

我想將你寫的這個程式套到以下這幾個模式中，目前只先嘗試"訂單明細表",但一直不成功！可否請你示範下如何修改程式內容？    出貨文件連結.rar (97.66 KB)

來源檔 Q:\00_科毅\出貨文件連結\FromERP\
訂單明細表.xlsx
請購單明細表.xlsx
採購單明細表.xlsx
進貨單明細表.xlsx
領料單明細表.xlsx
出貨單明細表.xlsx

目的檔 Q:\00_科毅\出貨文件連結\ERP_Data.xlsx
訂單.sheet
請購.sheet
採購.sheet
進貨.sheet
領料.sheet
出貨.sheet

準則存放的檔案及儲存格
Q:\00_科毅\出貨文件連結\VBA報表指令.xlsm
訂單明細表            H2
請購單明細表        H3
採購單明細表        H4
進貨單明細表        H5
領料單明細表        H6
出貨單明細表        H7

訂單明細表的套表模式為：
1. 以VBA報表指令.xlsm H2為準則,搜尋訂單明細表的B2欄位,當符合H2時(目前的值是MSO17060001)
2. 目前符合的儲存格是B11,則copy B11~BA的資料最底端,如果找不到代表沒有資料可複製.
3. 貼上資料至目的檔 Q:\00_科毅\出貨文件連結\ERP_Data.xlsx的"訂單.sheet",符合B欄MSO17060001的位置,並直接覆蓋原資料,選擇性貼上值(不要更改原資料的格式),如果找不到時,就當成是最新的資料,直接從目的檔的B欄最底端下一欄的空白列貼上(所以要能自動偵測資料的最末端,如果有全列空白(非全列空白不算是空白),其空白的第一列(若有空白列後再出現的資料視同空白)即是貼新資料的地方.

注意事項:
1. 來源檔及目的檔的資料都來自系統Download的報表,所以資料多寡是會變動的
2. 準則存放的H2~H7,其值也會隨著需求而變動
3. 目的檔每個sheet都有設定組成群組,是否應有先打開群組的動作,資料才會貼正確位置？
4. 當資料貼完以後,要再隱藏群組

TOP

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
9#
發表於 2017-7-25 23:32 | 顯示全部帖子
回復 12# GBKEE


大大,
我挑了其中一個檔來說明...
我看著它執行幾次，初時有點看不懂，因為來源檔及目的檔的資料都對不上，而且它執行過程中一直在存檔。後來我直接把來源檔及目的檔的單據號碼貼在一起比對，發現如下：
1) 目的檔的資料被排序過，而且只有單號排序，其他的欄沒有跟著變動，所以資料錯亂(其實我不要資料排序，我只要按原資料貼上而且是貼上值就好)
2) 目的檔的資料跟之前一樣,當來源檔資料比較少時,它無法Delete多餘的資料 (我一直在思考這個問題,如果這個部份的程式很難達成，乾脆所有的檔案在更新資料後，所有貼上的資料都維持反黑的狀態，意思就像下面的圖檔，更新的資料是B:BF15747,讓反黑保留下來,我一看就知道哪個區塊是更新過的，我再手動Delete 15748:15755，你覺得這樣如何？會不會比較好寫？


3) 執行過程中一直在存檔，這個部份我想只要最後存檔就好了，不然檔案大反而花時間一直在等待，而且我想ERP_Data.xlsx 我一直都在使用，改為存檔後不關閉.
4) ...其實我也暈了，不知道怎麼回覆比較好，等改好以上這些我再try看看有什麼問題吧.
5) 還有Y...我之前二個有關聯的發問放在同一個主題中都被你刪除了，我又要重新發問了，我已經先將其中一個較簡單的先PO上去，另一個正好我在作業當中，發現需求有變動，等我想好了確定要怎麼執行再PO上吧，當然我會用另一個主題，不會混在一起讓你發昏了。

TOP

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
10#
發表於 2017-7-26 19:42 | 顯示全部帖子
回復 13# PJChen

大大,

我今天一測試就卡住了，我不知道有什麼地方要修改，所以先PO上來給你看...
雖然刪了很多資料,檔案還是超過1MB,所以我有分割再上傳.

Try_20170726.part1.rar (600 KB)
Try_20170726.part2.rar (463.64 KB)

TOP

        靜思自在 : 得理要饒人，理直要氣和。
12
返回列表 上一主題