Index/Match using VBA not working

[rizzo93]

I've looked over some posts here related to my issue, but can't seem to get this to work.

I want to use Index/Match using VBA to find a specific cell value in a named range.

Dim myDistro As String
Dim myID As Integer
myDistro = Application.WorksheetFunction.Index(Worksheets("PS Console - Data").ListObjects("tblPSConsoleRAW").ListColumns("Distribution List"), Application.WorksheetFunction.Match(myID, Worksheets("PS Console - Data").ListObjects("tblPSConsoleRAW").ListColumns("ID"), 0))

The code above does not find the value I'm looking for, but if I change it to this, it will work:

myDistro = Application.WorksheetFunction.Index(Worksheets("PS Console - Data").Range("AR3:AR47"), Application.WorksheetFunction.Match(myID, Worksheets("PS Console - Data").Range("C3:C47"), 0))

My columns must be dynamic to capture the data as it changes. What am I missing?

 

[DanteAmor]

Dim myID As Integer

But in your code I don't see what value the myID variable has.

But assuming it has a value, you need to set the Range property

  myDistro = Application.WorksheetFunction.Index(Worksheets("PS Console - Data").ListObjects("tblPSConsoleRAW").ListColumns("Distribution List").Range, Application.WorksheetFunction.Match(myID, Worksheets("PS Console - Data").ListObjects("tblPSConsoleRAW").ListColumns("ID").Range, 0))

I recommend you use the following objects for your sheet and for your table to reduce that line of code and make reading more practical.

Sub index_match()
  Dim myDistro As String
  Dim myID As Integer
 
  Dim sh As Worksheet
  Dim tbl As ListObject
 
  Set sh = Sheets("PS Console - Data")
  Set tbl = sh.ListObjects("tblPSConsoleRAW")
 
  myID = 4    'for example
 
  myDistro = WorksheetFunction.Index(tbl.ListColumns("Distribution List").Range, _
             WorksheetFunction.Match(myID, tbl.ListColumns("ID").Range, 0))
End Sub

 

[rizzo93]

But in your code I don't see what value the myID variable has.

But assuming it has a value, you need to set the Range property

  myDistro = Application.WorksheetFunction.Index(Worksheets("PS Console - Data").ListObjects("tblPSConsoleRAW").ListColumns("Distribution List").Range, Application.WorksheetFunction.Match(myID, Worksheets("PS Console - Data").ListObjects("tblPSConsoleRAW").ListColumns("ID").Range, 0))

I recommend you use the following objects for your sheet and for your table to reduce that line of code and make reading more practical.

Sub index_match()
  Dim myDistro As String
  Dim myID As Integer
 
  Dim sh As Worksheet
  Dim tbl As ListObject
 
  Set sh = Sheets("PS Console - Data")
  Set tbl = sh.ListObjects("tblPSConsoleRAW")
 
  myID = 4    'for example
 
  myDistro = WorksheetFunction.Index(tbl.ListColumns("Distribution List").Range, _
             WorksheetFunction.Match(myID, tbl.ListColumns("ID").Range, 0))
End Sub

That worked, @DanteAmor! Thank you very much!