gunsix

測試過, 如果用 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

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

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