VBA Index Match across two worksheets

[RandiJ]

Hello,

I am trying to do an index match using vba with the indexed data coming from a different worksheet.

I was able to edit some code I found to pull the data from the same worksheet. I am now having trouble pulling the result from a different worksheet in the same workbook.

I tried referencing the worksheet but I am getting a formula error.

I am trying to pull the data from column E in the Data Source worksheet into Column E on the Result file worksheet. (Test file is attached)

VBA Test file1.xlsm

Sub INDEX_MATCH_Example3()

Dim k As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

k = 2

Do Until IsEmpty(Cells(k, 1))


Sheets("Result file").Cells(k, 5).Value = WorksheetFunction.Index(Sheets("Data source").Range("E:E"), WorksheetFunction.Match(Sheets("Data source").(Cells(k, 1).Value, Range("A:A"), 0))


k = k + 1

Loop

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

All help is appreciated.

[p45cal]

1. An extra open parentheses (red) in the line:
Sheets("Result file").Cells(k, 5).Value = WorksheetFunction.Index(Sheets("Data source").Range("E:E"), WorksheetFunction.Match(Sheets("Data source").(Cells(k, 1).Value, Range("A:A"), 0))
which shouldn't be there.
2.Unqualified range references in blue above, so they refer to whichever happens to be the active sheet at the time the code is executed.
3. WorksheetFunction.Index and WorksheetFunction.Match will both throw an error and stop the code executing if there is no match found. Use Application.Index and Application.Match instead (they still return an error but the code isn't halted).
4. Those changes leave:

Sheets("Result file").Cells(k, 5).Value = Application.Index(Sheets("Data source").Range("E:E"), Application.Match(Sheets("Data source").Cells(k, 1).Value, Sheets("Data source").Range("A:A"), 0))

which is looking at the wrong sheet for what to match, and should be:

Sheets("Result file").Cells(k, 5).Value = Application.Index(Sheets("Data source").Range("E:E"), Application.Match(Sheets("Result file").Cells(k, 1).Value, Sheets("Data source").Range("A:A"), 0))

This should work all right but it's long-winded.

You could get the code to put the formula in the cells, then convert the results of the formulae to plain values:

Sub blah()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Sheets("Result file")
  Set rngResult = Range(.Range("A2"), .Range("A1").End(xlDown)).Offset(, 4)
End With

rngResult.FormulaR1C1 = "=INDEX('Data source'!C,MATCH(RC1,'Data source'!C[-4],0))" 'put the formulae in the cells.
rngResult.Value = rngResult.Value 'convert to plain values.

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

but it gets easier than that; since they're both Tables you can:

Sub blah2()
With Range("Table1[Law Reference]")
  .FormulaR1C1 = "=INDEX(Table24[Law Reference],MATCH([@Fund],Table24[Fund],0))"
  .Value = .Value
End With
End Sub

[RandiJ]

Thank you so much. This works perfectly.