12
返回列表 上一主題 發帖

VAB 資料比對及轉寫

maggie1313

帖子
13 
主題
1 
精華
0 
積分
26 
點名
0  
作業系統
win7 
軟體版本
office2013 
閱讀權限
10 
性別
女 
註冊時間
2016-3-22 
最後登錄
2016-5-13 
1# 跳轉到 » 正序看帖
打印
tT
發表於 2016-3-22 15:31 | 只看該作者

VAB 資料比對及轉寫

大家好，最近要寫一個需求是，有二份資料表，二份資料表(資料表名稱：單頭、單身) 有一個共同的KEY 值(不會重覆)，二份彼此比對後，會將二份裡面的某些欄位轉入到另一個資料表，
但要有一個判斷是，轉入到另一個資料表的值必須先核對KEY值，不知大家懂不懂我的意思......哈哈，有點難表達




因為寫了一星期 ，卻發現愈寫沒頭緒一直寫不出來，請大家可以給我個方向，謝謝
收藏 分享

maggie1313

帖子
13 
主題
1 
精華
0 
積分
26 
點名
0  
作業系統
win7 
軟體版本
office2013 
閱讀權限
10 
性別
女 
註冊時間
2016-3-22 
最後登錄
2016-5-13 
20#
發表於 2016-4-8 14:59 | 只看該作者
回復 19# luhpro
你好，感謝你的回覆。但老實說我研究了一下，但還不是很了解你的意思 ，因為此份資料是要給前端的USER使用的，我才設定一個USERFORM的方式。
所以你所說的EXCEL中的篩選也有適用嗎?

TOP

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
16  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
19#
發表於 2016-4-8 00:04 | 只看該作者
回復  luhpro
...
關於第三個問題，我不是很清楚寫法。我已經有設計好一個userform的查詢畫面，裡面會有查詢的欄位目前設計三欄。
人員只要輸入查詢的資料(ps.有可能只輸入一筆，二筆，或三筆不一定)，但系統要住他所輸入的資料穩和，才會將查詢的資料「合併資料」內容複製到「查詢」的頁簽。 ...
maggie1313 發表於 2016-4-6 17:51

Excel 有個 進階篩選 的功能, 你可以試試 :

先把 User 輸入的條件依序放入 C14 ~ E14,
再執行下列指令:
  1. [A1].CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=[C13:E14], CopyToRange:=[A22], Unique:=False
複製代碼
最後再把結果 [A22].CurrentRegion  Copy 過去即可.

TOP

maggie1313

帖子
13 
主題
1 
精華
0 
積分
26 
點名
0  
作業系統
win7 
軟體版本
office2013 
閱讀權限
10 
性別
女 
註冊時間
2016-3-22 
最後登錄
2016-5-13 
18#
發表於 2016-4-6 17:51 | 只看該作者
回復 16# luhpro
感謝你的協助與指教。關於第一個問題我決定還是用變數的方式，因為我是初學者第一次寫，這樣我比較清楚。但還是很感謝
關於第三個問題，我不是很清楚寫法。我已經有設計好一個userform的查詢畫面，裡面會有查詢的欄位目前設計三欄。
人員只要輸入查詢的資料(ps.有可能只輸入一筆，二筆，或三筆不一定)，但系統要住他所輸入的資料穩和，才會將查詢的資料「合併資料」內容複製到「查詢」的頁簽。

20160406.rar (36.42 KB)

TOP

maggie1313

帖子
13 
主題
1 
精華
0 
積分
26 
點名
0  
作業系統
win7 
軟體版本
office2013 
閱讀權限
10 
性別
女 
註冊時間
2016-3-22 
最後登錄
2016-5-13 
17#
發表於 2016-4-6 08:52 | 只看該作者
回復 7# luhpro
了解，謝謝。我會在試試

TOP

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
16  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
16#
發表於 2016-4-2 22:14 | 只看該作者
本帖最後由 luhpro 於 2016-4-2 22:28 編輯
回復  luhpro
目前測試都OK。還有幾個問題想要在請教
一、如果來源單頭的資料還有多筆資料要載入「合併資 ...
maggie1313 發表於 2016-4-1 11:42

我在 2# 有說了喔 :
如果需要用到整列中不只一欄的資料,
可以索引 列號:
vD1(CStr(.Cells(lRow, 1))) = lRow
再用
Sheets("合併").Cells(lRow, 2) = Sheets("索引一").Cells(vD1(sStr), 欄號)
來取得想要的資料.
只要變更欄號就可以抓該筆資料中的任一個資料,
重點是要先取得該筆資料的列號,
查詢時也是以 Key 值(列號)來索引資料的.

所以:
vD1(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9))) = .Cells(lRow, 14)
vD2(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9))) = .Cells(lRow, 15)
...
MixDB.Cells(lRow, 3) = vD1(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9)))
MixDB.Cells(lRow, 5) = vD2(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9)))

可以改成:

vD1(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9))) = lRow
...
MixDB.Cells(lRow, 3) = EFhand.Cells(VD1(CStr(MixDB.Cells(lRow, 1) & "-" & Right(MixDB.Cells(lRow, 2), 12))),14) ' 只抓前 12 個字(201602220001)
MixDB.Cells(lRow, 5) = EFhand.Cells(VD1(CStr(MixDB.Cells(lRow, 1) & "-" & Right(MixDB.Cells(lRow, 2), 12))),15)
程式變很長,但實際可依你需求善用 With 與 End With 簡化及利於判讀.

至於 VD2 可以留給 MixDB 用 .

三、另外我想要在請教一個問題...

vD2(CStr(MixDB.Cells(lRow, 1) & "~" & MixDB.Cells(lRow, 2)  = lRow ' 請留意同樣為避免誤判,這裡改用 "~" 而非 "-"  來做區隔.
則 單頭金額 可用 MixDB.Cells(VD2(CStr(TextBox1 & "~"  & TextBox2 & "-"  & TextBox3)), 3) 來抓 ' 假設 TextBox? 分別放 單身單別 、 單身單號 及 序號

二、另外程式中有一段我不是很清楚用意...

這只是重複驗證執行結果前清資料用的,
看你實際的需求不見得要放.

TOP

maggie1313

帖子
13 
主題
1 
精華
0 
積分
26 
點名
0  
作業系統
win7 
軟體版本
office2013 
閱讀權限
10 
性別
女 
註冊時間
2016-3-22 
最後登錄
2016-5-13 
15#
發表於 2016-4-1 11:42 | 只看該作者
回復 13# luhpro
目前測試都OK。還有幾個問題想要在請教
一、如果來源單頭的資料還有多筆資料要載入「合併資料」的話，是要在多設定多個變數嗎?例：VD2
       因為現在我這樣設定是OK的
二、另外程式中有一段我不是很清楚用意。但我也沒有寫上去，但也是可行，沒有錯誤訊息。
三、另外我想要在請教一個問題，假設我的合併資料都齊全了，要設計一個查詢的表單畫面，讓人員查詢後，將查詢的結果另外在產生一個頁面，但內容是跟原有的「合併資料」頁簽相同 活頁簿1.rar (27.69 KB)

TOP

maggie1313

帖子
13 
主題
1 
精華
0 
積分
26 
點名
0  
作業系統
win7 
軟體版本
office2013 
閱讀權限
10 
性別
女 
註冊時間
2016-3-22 
最後登錄
2016-5-13 
14#
發表於 2016-4-1 11:10 | 只看該作者
回復 13# luhpro
謝謝指導，有加入註解比較清楚了。如有問題再請教。萬分感謝

TOP

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
16  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
13#
發表於 2016-4-1 03:08 | 只看該作者
本帖最後由 luhpro 於 2016-4-1 03:14 編輯
回復  luhpro
你好，感謝你的解答。測試後是OK的。但現在我有另個問題想請問，如圖
就是我們的合併資料要 ...
maggie1313 發表於 2016-3-30 11:12

同我 2# 所述,
這裡依你結果資料來看只需要抓單一資料,
故 Dictionary 直接存該資料.
  1. Sub DATABASE()
  2.   Dim lRow& ' 列號
  3.   Dim wsSou1 As Worksheet, wsSou2 As Worksheet, wsTar As Worksheet '工作表
  4.   Dim vD
  5.   
  6.   Set vD = CreateObject("Scripting.Dictionary")
  7.   Set wsSou1 = Sheets("EF單頭")
  8.   Set wsSou2 = Sheets("EF單身")
  9.   Set wsTar = Sheets("合併資料")
  10.   
  11.   With wsTar ' With wsTar 與 End With 間若有以 . 開頭的指令, 其 . 等同 wsTar (亦即 Sheets("合併資料"))
  12.     .Range(.[A2], .[D50]).Clear ' 等同 wsTar.Range(wsTar.[A2], wsTar.[D50]).Clear  .[A2]=wsTar.Range("A2")
  13.   End With
  14.   
  15.   lRow = 1 ' 因為沒有標題列, 故從第1列開始抓
  16.   With wsSou1 ' EF單頭
  17.     While .Cells(lRow, 8) <> "" ' Cells(列號, 欄號) 欄號 8 = H 攔, 逐列抓取資料
  18.      vD(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9))) = .Cells(lRow, 14) ' vD("ODMGET_M00602-201602220001") = 4355 若只要抓金額,那就直接放金額
  19.       lRow = lRow + 1
  20.     Wend
  21.   End With
  22.   
  23.   lRow = 2
  24.   With wsSou2 ' EF單身
  25.     wsTar.Range(wsTar.[A2], wsTar.[D20]).Clear ' 清除上次產生的資料, 正式使用可以刪掉此行
  26.     While .Cells(lRow, 8) <> ""
  27.       wsTar.Cells(lRow, 1) = .Cells(lRow, 8)
  28.       wsTar.Cells(lRow, 2) = .Cells(lRow, 9) & "-" & .Cells(lRow, 10)
  29.         ' 用 "-" 是因為 鍵(Key, 索引)值 內沒有 "-", 故以其做為兩攔資料的銜接符號,避免類似111222333 加上 123456 與 1112223331 加上 23456 兩者會視為相同的問題.
  30.       wsTar.Cells(lRow, 3) = vD(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9))) ' 從 Dictionary 中索引 EF單頭 的金額
  31.       wsTar.Cells(lRow, 4) = .Cells(lRow, 18) ' 從 R 攔(欄號 = 18)直接抓金額
  32.       lRow = lRow + 1
  33.     Wend
  34.   End With
  35. End Sub
複製代碼
VAB 資料比對及轉寫.zip (24.85 KB)

補充 :  剛剛才看到 第二頁 的發文,
所以列號起始數字沒變,
你可以直接改成你需要的數字就可以了.

TOP

maggie1313

帖子
13 
主題
1 
精華
0 
積分
26 
點名
0  
作業系統
win7 
軟體版本
office2013 
閱讀權限
10 
性別
女 
註冊時間
2016-3-22 
最後登錄
2016-5-13 
12#
發表於 2016-3-30 14:04 | 只看該作者
回復 10# maggie1313
不好意思，電腦異常，故重新發送關於「合併資料」從第三行開始的圖片
檔案.rar (24.05 KB)

TOP

        靜思自在 : 人生最大的成就是從失敗中站起來。
12
返回列表 上一主題