VBA Excel insert-match function from other workbook-CodePudding

I am trying implement index match function in for loop . I want to copy value from other workbook to active cell workbook.

here is code that i write:

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

CodePudding user response：

Please, try replacing of:

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

with:

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

If Application.WorksheetFunction.Match(... does not find a match, it raises an error...

CodePudding user response：

Different approach but it works.

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

End Sub