VBA Code for IndexMatch Function not working without error

Hi All,

Attachment 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:

Code:

---

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

1. Typo -- see screenshot

Attachment 28738

2. Not sure about the rest of your logic

3. Not sure why you wanted to use Index. I'd use VLookup and simplify

Code:

---

Option Explicit


Sub IndexMatch()


    Dim wsDestination As Worksheet, wsPopulation As Worksheet
    Dim n As Long
    Dim rPopulation As Range, rDestination As Range


    Set wsDestination = ThisWorkbook.Worksheets("Destination")
    Set wsPopulation = ThisWorkbook.Worksheets("Population")


    Set rPopulation = wsPopulation.Range("A:B")
    Set rDestination = wsDestination.Cells(1, 1).CurrentRegion
   
    For n = 2 To rDestination.Rows.Count
        On Error Resume Next
        wsDestination.Cells(n, 3).Value = Application.WorksheetFunction.VLookup(wsDestination.Cells(n, 1).Value, rPopulation, 2, 0)
        On Error GoTo 0
    Next n


End Sub

---

Thank you so much for your suggestions, it works!