Board logo

標題: VBA 內 VLOOKUP 問題 [打印本頁]
作者: gunsix    時間: 2020-9-4 00:06     標題: VBA 內 VLOOKUP 問題

例如本來的 formula 係...
=VLOOKUP(B2,StaffList.CSV!A:Z,{2,9,10,12,13,18,19},FALSE)

放在 VBA 行會變成
=VLOOKUP(B2,StaffList.CSV!A:Z,@{2,9,10,12,13,18,19},FALSE)

無端端攝個 @ 入去 column index 度, 於是出 error

亦試過用 array 裝住 column index, 佢又係攝個 @ 入去...

請問我係咪做漏咗啲野? 多謝指教
作者: zeon13    時間: 2020-9-4 09:27

你直接喺VBA寫上面段CODE? 寫法唔同
google下VBA+vlookup
作者: myricky    時間: 2020-9-4 09:52

提示: 作者被禁止或刪除 內容自動屏蔽
作者: gunsix    時間: 2020-9-4 13:14

本帖最後由 gunsix 於 2020-9-4 16:04 編輯

Sorry 係我問得唔好, 故事係咁的...

我初學 VBA 的, 因為經常要將兩個 file 拍埋, 由第二個 file 抽 7 個 column 出嚟貼入第一個 file

用 macro recorder 錄, 佢生成 ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],StaffList.CSV!C1:C19,2,FALSE)", 同一句出現 7 次, 因為我逐個 column 抽, 抽 7 次

我改成 ActiveCell.Formula = "=VLOOKUP(B2,StaffList.CSV!A:Z,2,FALSE)", 因為 reference cell 永遠都係 B2, 同埋易睇啲, 不過一樣要寫 7 次

我想用 For loop 簡化成一句畀佢自己行 7 次, 於是用一個 array 裝住果 7 個 column 位置, 然後改成...

ActiveCell.Formula = "=VLOOKUP(B2,StaffList.CSV!A:Z,StaffListColumn[ i ] ,FALSE)"

不過行完個 cell 變成 =VLOOKUP(B2,StaffList.CSV!A:Z,@StaffListColumn[1],FALSE), 唔成功, 多咗個 @

尋晚 google 一下, 原來有所謂 array forumla, 寫成 =VLOOKUP(B2,StaffList.CSV!A:Z,{2,9,10,12,13,18,19},FALSE) 就唔使行 7 次

於是我在 VBA 入面改成 ActiveCell.Formula = "=VLOOKUP(B2,StaffList.CSV!A:Z,{2,9,10,12,13,18,19},FALSE)"

點知都係變成 =VLOOKUP(B2,StaffList.CSV!A:Z,@{2,9,10,12,13,18,19},FALSE), 又係多咗個 @

原 code 好長, 下面果段出現 7 次, 只係紅色個字唔同

    Range("D1").Select
    ActiveCell.Formula = "Email Banner"
    Range("D2").Select
    ActiveCell.Formula = "=VLOOKUP(B2,StaffList.CSV!A:Z,2,FALSE)"
    Selection.AutoFill Destination:=Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select
作者: myricky    時間: 2020-9-4 15:06

提示: 作者被禁止或刪除 內容自動屏蔽
作者: gunsix    時間: 2020-9-4 16:11

回覆  gunsix

我唔知除左row2, 仲有幾多row仲要lookup col B, 所以用左col B最後個row做control, 同時當 ...
myricky 發表於 2020-9-4 03:06 PM


Thanks a lot, 我仲係左抄右抄一知半解, 不過從你的 code 中我明白問題係我唔明 " " 入面的操作, 學你咁用 & 切開就可以了, 即係...

ActiveCell.Formula = "=VLOOKUP(B2,StaffList.CSV!A:Z," & StaffListColumn(1) & ",FALSE)"

測試成功 !

餘下係點樣用 For loop 逐個 cell 行, 會再研究你的 code

Thanks a lot!
作者: gunsix    時間: 2020-9-4 16:21

嗯, 不過如果係...

ActiveCell.Formula = "=VLOOKUP(B2,StaffList.CSV!A:Z," & "{2,9,10,12,13,18,19}" & ",FALSE)"

仍然變成...

=VLOOKUP(B2,StaffList.CSV!A:Z,@{2,9,10,12,13,18,19},FALSE)

奇怪...
作者: myricky    時間: 2020-9-4 19:52

提示: 作者被禁止或刪除 內容自動屏蔽
作者: gunsix    時間: 2020-9-4 23:53

試下轉為 Activecell.FormulaArray=
myricky 發表於 2020-9-4 07:52 PM


Thanks, 剛試咗, 個 cell value 變成

{=VLOOKUP(B2,StaffList.CSV!A:Z,{2,9,10,12,13,18,19},FALSE)}

冇攝 @ 了, 不過亦唔識爆開 7 個 cell...

我諗放棄呢招了, 反正上面已解決用唔到 array 的問題, 一於用 array 吧
作者: gunsix    時間: 2020-9-5 00:26

回覆  gunsix

我唔知除左row2, 仲有幾多row仲要lookup col B, 所以用左col B最後個row做control, 同時當 ...
myricky 發表於 2020-9-4 03:06 PM


我想請教一下你段 code, 第一句入面 Cells(10000, "b"), 點解用 10000? 我理解成句係用嚟攞最後一行個數目, 不過唔明點解 10000

你個方法係用 For loop 將每個 cell 都放一個 VLOOKUP, 咁同只將 VLOOKUP 放第一行然後 Selection.AutoFill 向下抄落去, 運算時間係咪其實一樣?

多謝指教
作者: gunsix    時間: 2020-9-5 13:18

本帖最後由 gunsix 於 2020-9-5 13:21 編輯

測試過, 如果用 For loop 將 VLOOKUP 逐行抄會太慢 (個 file 有 12 萬行), 都係 autofill 快好多, 最後用以下完成...
  1.    
  2. lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
  3. lastRow = Cells(Rows.Count, 1).End(xlUp).Row
  4. For k = 1 To 7
  5.     Cells(1, lastColumn + k).Select
  6.     ActiveCell.Formula = Workbooks("StaffList.CSV").Worksheets("StaffList").Cells(1, staffListColumn(k))
  7.     Cells(2, lastColumn + k).Select
  8.     ActiveCell.Formula = "=VLOOKUP(B2,StaffList.CSV!A:Z," & staffListColumn(k) & ",FALSE)"
  9.     Selection.AutoFill Destination:=Range(Cells(2, lastColumn + k).Address, Cells(lastRow, lastColumn + k).Address)
  10.     Range(Selection, Selection.End(xlDown)).Select
  11. Next
複製代碼
呢個方法係每個 column 各自抄落去, 其實可唔可以 7 個 column 一齊抄落去呢? 試改成以下...
  1. lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
  2. lastRow = Cells(Rows.Count, 1).End(xlUp).Row
  3. For k = 1 To 7
  4.     Cells(1, lastColumn + k).Select
  5.     ActiveCell.Formula = Workbooks("StaffList.CSV").Worksheets("StaffList").Cells(1, staffListColumn(k))
  6.     Cells(2, lastColumn + k).Select
  7.     ActiveCell.Formula = "=VLOOKUP(B2,StaffList.CSV!A:Z," & staffListColumn(k) & ",FALSE)"
  8. Next
  9. Selection.AutoFill Destination:=Range(Cells(2, lastColumn + 1).Address, Cells(lastRow, lastColumn + 7).Address)
  10. Range(Selection, Selection.End(xlDown)).Select
複製代碼
奇怪只係抄了 6 個 column, 同埋變成 =VLOOKUP(#REF!,StaffList.CSV!#REF!,19,FALSE), 似乎佢只係將第二行最後一個 cell 的 VLOOKUP 抄晒落去, 唔係成個第二行抄落去...
作者: gunsix    時間: 2020-9-5 13:28

Oh... 解決了, 只要嚮 Selection.AutoFill 之前加一行就可以了

Range(Cells(2, lastColumn + 1).Address, Cells(2, lastColumn + 7).Address).Select




歡迎光臨 電腦領域 HKEPC Hardware (https://h0.hkepc.com/forum/) Powered by Discuz! 7.2