CODEWITHSUNDEEP
excelvba
Kurt
Kurt

Reputation: 135

Error Handling with Index Match function in VBA Excel

I am quite new to VBA and I have implemented an index/match function that returns data correctly. The only thing that needs to be corrected is about how this code handles the values that are not matched. In this case values that don't have a match return the #N/A error while I would like to return simply zero.

  Sub IndexMatch()

Dim destinationWs As Worksheet, dataWs As Worksheet
Dim destinationLastRow As Long, dataLastRow As Long, x As Long
Dim IndexRng As Range, IndexRng2 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("B2:B" & dataLastRow)
Set MatchRng = dataWs.Range("A2:A" & dataLastRow)


For x = 2 To destinationLastRow

On Error Resume Next
    
a = Application.Match(destinationWs.Range("A" & x).Value, MatchRng, 0)

If IsError(a) Then
    b = 0
Else
    b = Application.Index(IndexRng, a)
End If
       
'destinationWs.Range("C" & x).Value = Application.Index( _
'IndexRng, Application.Match(destinationWs.Range("A" & x).Value, MatchRng, 0))

On Error GoTo 0

Next x
    
    End Sub

How can I translate that in the above code? Thanks in advance

Upvotes: 1

Views: 1009

Answers (2)

Nathan_Sav
Nathan_Sav

Reputation: 8531

Use the application version Match, like so where b would be your destination range.

a = Application.Match("XYZ", Range("F1:F100"), 0)

If IsError(a) Then
    b = 0
Else
    b = Application.WorksheetFunction.Index(Range("F1:F100"), a)
End If

Upvotes: 2

Kurt
Kurt

Reputation: 135

Here is the full code:

Sub IndexMatch()

Dim destinationWs As Worksheet, dataWs As Worksheet
Dim destinationLastRow As Long, dataLastRow As Long, x As Long
Dim IndexRng As Range, IndexRng2 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("B2:B" & dataLastRow)
Set MatchRng = dataWs.Range("A2:A" & dataLastRow)


For x = 2 To destinationLastRow

On Error Resume Next
    
a = Application.Match(destinationWs.Range("A" & x).Value, MatchRng, 0)

If IsError(a) Then
    b = 0
Else
    b = Application.Index(IndexRng, a)
End If
       
'destinationWs.Range("C" & x).Value = Application.Index( _
'IndexRng, Application.Match(destinationWs.Range("A" & x).Value, MatchRng, 0))

On Error GoTo 0

Next x
    
    End Sub

Upvotes: 0

Related Questions