VBA Code for IndexMatch Function not working without error

[Claudio1978]

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

[Paul_Hossler]

1. Typo -- see screenshot

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



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

[Claudio1978]

Thank you so much for your suggestions, it works!