How do I use listobjects for Index/Match in VBA?

[rizzo93]

I'm want to use tables and column names when using index/match with vba. I can get my formula to work when I enter a fixed range like this:

Sub NotAckMe()
Dim sourceTbl, apolloProcessed As ListObject
Dim apollo As Worksheet
Dim sourceRows As Integer
Dim estimator, b As String

    Set apollo = Worksheets("Apollo Processed")
    Set apolloProcessed = apollo.ListObjects("q_ApolloProcessed")
    Set sourceTbl = mySource.ListObjects("tblNotAcknowledged")
    sourceRows = sourceTbl.DataBodyRange.Rows.Count

    For r = 5 To sourceRows + 4
        estimator = mySource.Cells(r, 2)
        b = Application.WorksheetFunction.Index(apollo.Range("G3:G121"), Application.WorksheetFunction.Match(estimator, apollo.Range("B3:B121"), 0))
    Next r

End Sub

But what I want to do is replace the ranges above (in the For loop) to reference the columns within my table (apolloProcessed).

What am I missing, please?

 

[Fluff]

It should be

Dim sourceTbl As ListObject

singular rather than plural.

 

[rizzo93]

It should be

Dim sourceTbl As ListObject

singular rather than plural.

Interesting. Ok, so singular when declaring it, but plural when you are assigning a variable to a table. It worked though.

With that in mind, I'll go back to the code we worked with yesterday and see if there's any discrepancy in that regard.

 

[Fluff]

Ok, so singular when declaring it, but plural when you are assigning a variable to a table.

That's right you are assigning a single table (listobject) from the collection of all tables (listobjects )

 

[rizzo93]

That's right you are assigning a single table (listobject) from the collection of all tables (listobjects )

I figured out what the problem was. I had the rngReturn and rngMatch referring to the wrong columns. Once I made the switch, it worked out.

Fluff, I REALLY appreciate your help. Thank you very much!

 

[Fluff]

Glad you sorted it & thanks for the feedback.