VBA Index Match on a table help

[JashVash]

Hello,

I am trying to do an index match from one sheet to the other I am unsure of what I am doing wrong, nothing is populating when I run the code and there are no syntax errors.

I have Sheets("IFS Upload") Referencing to a table from another sheet named Sheets("Category")

My code is shown below and the references I am trying to index match, am I missing a step?
Do I need to also define where the table is?
Please help me.

Dim Ifs As Worksheet
Dim LastRow As Long
Dim Category As Integer

Set Ifs = ThisWorkbook.Sheets("IFS Upload")

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
For x = 2 To LastRow
    On Error Resume Next
    If Len(Ifs.Range("N" & x).Value) > 0 Then
     Ifs.Range("O" & x) = WorksheetFunction.Index(Range("jobtype[Trade]"), WorksheetFunction.Match("N" & x, Range("jobtype[IFS]"), 0))
   
    
End If

Next x

 

[Alex Blakenburg]

The reason you are not getting an error is because your On Error Resume Next is masking it.

Change your formula line to the below:
The change is putting you "N" & x inside of Range()

         Ifs.Range("O" & x) = WorksheetFunction.Index(Range("jobtype[Trade]"), WorksheetFunction.Match(Range("N" & x), Range("jobtype[IFS]"), 0))

 

[JashVash]

The reason you are not getting an error is because your On Error Resume Next is masking it.

Change your formula line to the below:
The change is putting you "N" & x inside of Range()

         Ifs.Range("O" & x) = WorksheetFunction.Index(Range("jobtype[Trade]"), WorksheetFunction.Match(Range("N" & x), Range("jobtype[IFS]"), 0))

Thank you Alex,

I have tried and removed the on error. But I am getting this error now, Do you know if I have to define the work sheet where the table is?

 

[Alex Blakenburg]

No you don't.
What you need is some code that handles the error of not finding a match.
I have logged off for the night but can do it tomorrow.

 

[JashVash]

No you don't.
What you need is some code that handles the error of not finding a match.
I have logged off for the night but can do it tomorrow.

Thank you Alex I appreciate your help! Have a good night! Looking forward to your help tomorrow! Cheers!

 

[Alex Blakenburg]

See if this works for you.

        With Application
            Ifs.Range("O" & x) = .IfError(.Index(Range("jobtype[Trade]"), .Match(Range("N" & x), Range("jobtype[IFS]"), 0)), "")
        End With

Using WorksheetFunction gives you access to Intellisense but removing it and relying on Application. allows you to use normal error handling (IfError or IsError) as opposed to having to handle VBA errors (On Error)

 

[JashVash]

See if this works for you.

        With Application
            Ifs.Range("O" & x) = .IfError(.Index(Range("jobtype[Trade]"), .Match(Range("N" & x), Range("jobtype[IFS]"), 0)), "")
        End With

Using WorksheetFunction gives you access to Intellisense but removing it and relying on Application. allows you to use normal error handling (IfError or IsError) as opposed to having to handle VBA errors (On Error)

Thank you Alex I figured it out it was referencing the wrong reference. and Managed to get it to work! Thank you so much for your help!!! I appreciate it!
Hope you have a good one!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.