RandiJ
10-09-2020, 06:17 PM
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)
27287
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.
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)
27287
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.