Claudio1978
07-14-2021, 01:13 AM
Hi All,
28735
I m trying to implement an index-match through VBA and although everything seems like correct to me, I cannot figure out why it's not working.
I attached a file in this post to help you guys understand what's now working.
The code I used is the below one:
Sub IndexMatch()
Dim destinationWs As Worksheet, dataWs As Worksheet
Dim destinationLastRow As Long, dataLastRow As Long, x As Long
Dim IndexRng As Range, MatchRng As Range
Set destinationWs = ThisWorkbook.Worksheets("Destination")
Set dataWs = ThisWorkbook.Worksheets("Population")
destinationLastRow = destinationWs.Range("A" & Rows.Count).End(xlUp).Row
dataLastRow = dataWs.Range("A" & Rows.Count).End(xlUp).Row
Set IndexRng = dataWs.Range("A2:A" & dataLastRow)
Set MatchRng = dataWs.Range("C2:C" & dataLastRow)
For x = 2 To destinationLastRow
On Error Resume Next
destinationWs.Range("C" & x).Value = Application.WorksheetFunction.Index( _
IndextRng, _
Application.WorksheetFunction.Match(destinationWs.Range("A" & x).Value, MatchRng, 0))
Next x
End Sub
Thank you in advance for your help
28735
I m trying to implement an index-match through VBA and although everything seems like correct to me, I cannot figure out why it's not working.
I attached a file in this post to help you guys understand what's now working.
The code I used is the below one:
Sub IndexMatch()
Dim destinationWs As Worksheet, dataWs As Worksheet
Dim destinationLastRow As Long, dataLastRow As Long, x As Long
Dim IndexRng As Range, MatchRng As Range
Set destinationWs = ThisWorkbook.Worksheets("Destination")
Set dataWs = ThisWorkbook.Worksheets("Population")
destinationLastRow = destinationWs.Range("A" & Rows.Count).End(xlUp).Row
dataLastRow = dataWs.Range("A" & Rows.Count).End(xlUp).Row
Set IndexRng = dataWs.Range("A2:A" & dataLastRow)
Set MatchRng = dataWs.Range("C2:C" & dataLastRow)
For x = 2 To destinationLastRow
On Error Resume Next
destinationWs.Range("C" & x).Value = Application.WorksheetFunction.Index( _
IndextRng, _
Application.WorksheetFunction.Match(destinationWs.Range("A" & x).Value, MatchRng, 0))
Next x
End Sub
Thank you in advance for your help