我正在嘗試使用 vba 將值從一列復制到另一列。我正在使用以下 vba 腳本：
Private Sub Import_Click()
Worksheets("test").Range("D10:D49") = Worksheets("test2").Range("G22:G61").Value
End Sub
但這只是將值從一列復制到另一列。我的問題是這樣的，考慮下面的例子：
我想通過將“Num”與“專案”匹配來將表 1 中的“Num”復制到表 2。有沒有辦法使用VBA來做到這一點？因為，我的實際清單真的很長。
uj5u.com熱心網友回復：
如果您正在處理大量資料并想使用 VBA，您可以使用動態陣列。
試試這個例子：
我已經復制了您的示例，假設第一個表位于 A 和 B 列，第二個 E 和 F （都在第一行）：
Sub lookup_with_arrays()
Dim wb As Workbook
Dim ws As Worksheet
Dim arr1(), arr2() As Variant
Dim lastrow_arr1, lastrow_arr2, i, j As Long
Set wb = Workbooks("Your_File.xlsm")
Set ws = wb.Worksheets("Your_Sheet")
lastrow_arr1 = Range(ws.Cells(1, 1), ws.Cells(1, 1).End(xlDown)).Rows.Count
lastrow_arr2 = Range(ws.Cells(1, 5), ws.Cells(1, 5).End(xlDown)).Rows.Count
'Set dynamic dimensions
ReDim arr1(1 To lastrow_arr1, 1 To 2)
ReDim arr2(1 To lastrow_arr2, 1 To 2)
'Indicate which data to set up in the arrays
For i = LBound(arr1) To UBound(arr1)
arr1(i, 1) = ws.Cells(i, 1)
arr1(i, 2) = ws.Cells(i, 2)
Next i
For i = LBound(arr2) To UBound(arr2)
arr2(i, 1) = ws.Cells(i, 5)
arr2(i, 2) = ws.Cells(i, 6)
Next i
'Now we can match both Items colums and complete arr2 second column
For i = LBound(arr1) To UBound(arr1)
For j = LBound(arr2) To UBound(arr2)
If arr1(i, 1) = arr2(j, 1) Then
arr2(j, 2) = arr1(i, 2)
Exit For
End If
Next j
Next i
'Then you can report arr2 in your worksheet
For i = 2 To UBound(arr2)
ws.Cells(i, 6) = arr2(i, 2)
Next i
End Sub
另一種選擇是使用 Vlookup 函式：
Function VLOOKUP(TheValueYouNeed As Variant, RangeOfSearch As Range, No_index_col As Single, Optional CloseValue As Boolean)
On Error GoTo VLookUpError
VLOOKUP = Application.VLOOKUP(TheValueYouNeed, RangeOfSearch, No_index_col, CloseValue)
If IsError(VLOOKUP) Then VLOOKUP = 0
Exit Function
VLookUpError:
VLOOKUP = 0
End Function
我不是該函式的創建者，但我不記得在哪里找到它（無論如何謝謝）
然后幾乎就像在 excel 中一樣使用它：
Sub lookup_using_function()
Dim lastrow_arr1, lastrow_arr2, i As Long
Dim looked_item As Variant
Dim search_table As Range
Dim col_num As Single
Dim bool As Boolean
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("Your_File.xlsm")
Set ws = wb.Worksheets("Your_Sheet")
lastrow_arr1 = Range(ws.Cells(1, 1), ws.Cells(1, 1).End(xlDown)).Rows.Count
lastrow_arr2 = Range(ws.Cells(1, 5), ws.Cells(1, 5).End(xlDown)).Rows.Count
Set search_table = ws.Range("A:B")
col_num = 2
bool = False
For i = 2 To lastrow_arr2
looked_item = ws.Cells(i, 5)
ws.Cells(i, 6) = VLOOKUP(looked_item, search_table, col_num, bool)
Next i
然后我通常插入一個表單，右鍵單擊它以分配一個宏。單擊時執行分配的宏。
根據您的評論進行編輯：
Cells() 適用于坐標。
例如 ws.Cells(5,4) 代表名為 ws 的作業表中第 4 列的第 5 行單元格。
因此，如果您的表格從第 6 行和第 3 列開始：
'Indicate which data to set up in the arrays (i 5 instead of i)
For i = LBound(arr1) To UBound(arr1)
arr1(i, 1) = ws.Cells(i 5, 3)
arr1(i, 2) = ws.Cells(i 5, 4)
Next i
LBound 和 Ubound 對于為整個陣列設定 for 回圈很有用。
要遍歷行：
For i=LBound(arr1) to UBound(arr1)
Next i
要遍歷列，您需要提供附加引數 2（默認為 1）
For i=LBound(arr1, 2) to UBound(arr1, 2)
Next i
如果您的表有不同的列，您可能還必須遍歷列以指定您想要的資料：
For i=LBound(arr1) to UBound(arr1)
For j=LBound(arr1, 2) to UBound(arr1, 2)
arr1(i, j) = ws.Cells(i 5, j 2)
Next j
Next i
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/526511.html
上一篇：如果檔案已經存在，則會出錯-將檔案移動到另一個檔案夾
下一篇：慢跑宏