VBA Index Match (Error Handling)

[ElRoober]

Hello Everyone,

I am a super basic vba user. I was wondering if someone would be able to show me how to adjust the code below to return the value "No Data" if a value is not there and to continue rather than throwing an error.

Thanks you so much


Sub IndexMatch()

Dim i As Integer

'Perform index match
For i = 2 To 11
Cells(i, 6).Value = WorksheetFunction.Index(ThisWorkbook.Worksheets("sheet1").Range("A2:A11"), _
WorksheetFunction.Match(Cells(i, 4).Value, ThisWorkbook.Worksheets("sheet1").Range("B2:B11"), 0))

Next i

End Sub

 

[kevin9999]

Here's an alternative method to try:

With Range("F2:F11")
    .FormulaR1C1 = "=IFERROR(INDEX(R2C1:R11C1,MATCH(RC4,R2C2:R11C2,0)),""No Data"")"
    .Value = .Value
End With

 

[ElRoober]

Here's an alternative method to try:

With Range("F2:F11")
    .FormulaR1C1 = "=IFERROR(INDEX(R2C1:R11C1,MATCH(RC4,R2C2:R11C2,0)),""No Data"")"
    .Value = .Value
End With

Hi thanks for getting back to me with this solution. Is there aways to amend it to reflect my range being on another sheet (sheet 1). Thank you again.

 

[kevin9999]

Certainly:

With Range("F2:F11")
    .FormulaR1C1 = "=IFERROR(INDEX('Sheet1'!R2C1:R11C1,MATCH(RC4,'Sheet1'!R2C2:R11C2,0)),""No Data"")"
    .Value = .Value
End With

 

[ElRoober]

Certainly:

With Range("F2:F11")
    .FormulaR1C1 = "=IFERROR(INDEX('Sheet1'!R2C1:R11C1,MATCH(RC4,'Sheet1'!R2C2:R11C2,0)),""No Data"")"
    .Value = .Value
End With

Works a charm! thank you so much for this; a massive help.

 

[kevin9999]

Happy to help, and welcome to the forum!