Having Troubles with Index Matching from a table

Status
Not open for further replies.

JashVash

New Member
Joined
Feb 23, 2022
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am having trouble with my code to reference from a table called "jobtype" I have the codes below and the full code below. The Macro runs with "test" text but as soon as I used the formula below of index match.
It just goes on an endless loop. Do I need to define where my table is first before it reads the range of the table since it is in a different sheet?

Application.WorksheetFunction.Index(Range("jobtype[Trade]"), Application.WorksheetFunction.Match(Range(cell3), Range("jobtype[IFS]"), 0))

Supporting code and spread sheet images are below.

Thank you!

VBA Code:
Sub IFSVlook()

Dim cell3 As Range
Dim Ifs As Worksheet
Dim LastRow As Long





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

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
   
For Each cell3 In Worksheets("IFS Upload").Range("N2:N" & LastRow)
On Error Resume Next
If Len(cell3.Value) > 0 Then
cell3.Offset(0, 1) = Application.WorksheetFunction.Index(Range("jobtype[Trade]"), Application.WorksheetFunction.Match(Range(cell3), Range("jobtype[IFS]"), 0))
  
   
End If

Next


 

End Sub


1655397158958.png

1655397251773.png
 

Attachments

  • 1655397124397.png
    1655397124397.png
    29.9 KB · Views: 0
  • 1655397177491.png
    1655397177491.png
    43.1 KB · Views: 1

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,761
Office Version
  1. 365
Platform
  1. Windows
Duplicate to: VBA Index Match on a table help

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.
 
Status
Not open for further replies.

Forum statistics

Threads
1,181,673
Messages
5,931,352
Members
436,787
Latest member
ogharipour

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top