PDA

View Full Version : [SOLVED:] VBA Code for IndexMatch Function not working without error



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

Paul_Hossler
07-14-2021, 08:19 AM
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
07-14-2021, 11:06 AM
Thank you so much for your suggestions, it works!