我正在嘗試在 for 回圈中實作索引匹配功能。我想將其他作業簿中的值復制到活動單元格作業簿。
這是我寫的代碼：
Sub Amazon_Index()
Dim WB As Workbook
Dim Partner As Worksheet
Dim AcWS As Worksheet
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With
Set WB = Workbooks.Open(Filename:="C:\Users\Adis\Desktop\Amazon narud?be\Partner lista\partner.xlsx")
Set Partner = WB.Sheets("lista")
For i = 2 To LastRow
ActiveSheet.Cells(i, 19) = _
Application.WorksheetFunction.Index(Sheets("Partner").Range("A1:E100"), _
Application.WorksheetFunction.Match(ActiveSheet.Cells(i, 18), _
Sheets("Partner").Range("B1:B100"), 0), 5)
Next
End Sub
uj5u.com熱心網友回復：
請嘗試更換：
For i = 2 To LastRow
ActiveSheet.Cells(i, 19) = _
Application.WorksheetFunction.Index(Sheets("Partner").Range("A1:E100"), _
Application.WorksheetFunction.Match(ActiveSheet.Cells(i, 18), _
Sheets("Partner").Range("B1:B100"), 0), 5)
Next
和：
dim mtch
For i = 2 To LastRow
mtch = Application.WorksheetFunction.Match(ActiveSheet.Cells(i, 18).value , _
Sheets("Partner").Range("B1:B100"), 0)
If not IsError(mtch) then
ActiveSheet.Cells(i, 19) = _
Application.WorksheetFunction.Index(Sheets("Partner").Range("A1:E100"), mtch, 5)
End If
Next
如果Application.WorksheetFunction.Match(...找不到匹配項，則會引發錯誤...
uj5u.com熱心網友回復：
不同的方法，但它的作業原理。
Sub Insert_Index() ' ' Insert_Index Macro ' Dim LastRow As Long With ActiveSheet LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row End With
Range("S2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX([partner.xlsx]lista!R1C1:R70C5,MATCH(RC[-1],[partner.xlsx]lista!R1C2:R70C2,0),1)"
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX([partner.xlsx]lista!R1C1:R70C5,MATCH(RC[-2],[partner.xlsx]lista!R1C2:R70C2,0),5)"
Range("S2:T2").Select
Selection.AutoFill Destination:=Range("S2:T" & LastRow), Type:=xlFillDefault
結束子
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/392496.html