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]

Try

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

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

    For R = 5 To sourceRows + 4
        estimator = mySource.Cells(R, 2)
        b = Application.Index(rngReturn, Application.Match(estimator, rngMatch, 0))
    Next R

End Sub

Change the two headernames to match your table headers

 

[rizzo93]

Try

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

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

    For R = 5 To sourceRows + 4
        estimator = mySource.Cells(R, 2)
        b = Application.Index(rngReturn, Application.Match(estimator, rngMatch, 0))
    Next R

End Sub

Change the two headernames to match your table headers

Thanks for replying, Fluff!

It's not working just yet. I modified my code a little more to be better organized. Note: I like the way you condensed the Dims at top, but I temporarily separated them to help me go through the process.

Sub NotAckMe()
Dim apollo As Worksheet
Dim notAckSource As Worksheet
Dim sourceTbl As ListObject
Dim apolloProcessed As ListObject
Dim notAckTbl As ListObject
Dim sourceRows As Integer
Dim estimator, b As String
Dim rngReturn As Range
Dim rngMatch As Range

    Set apollo = Worksheets("Apollo Processed")
    Set notAckSource = Worksheets("Not Ack Source")
    
    Set apolloProcessed = apollo.ListObjects("q_ApolloProcessed")
    Set notAckTbl = notAckSource.ListObjects("tblNotAcknowledged")
    sourceRows = notAckTbl.DataBodyRange.Rows.Count
    
    Set rngReturn = apolloProcessed.ListColumns("First/Last").DataBodyRange
    Set rngMatch = apolloProcessed.ListColumns("Intialed").DataBodyRange

    For R = 5 To sourceRows + 4
        estimator = notAckSource.Cells(R, 2)
        b = Application.Index(rngReturn, Application.Match(estimator, rngMatch, 0))
    Next R

End Sub

In case you were wondering, I acknowledge that the header name "Intialed" is misspelled. For now, I'm leaving it that way so that other things won't break.

When running this code, I get a Type mismatch error. It happens when I get to the line where "b=...".

 

[Fluff]

Do you have any cells in either the First/Last or Intialed columns that contains errors such as #N/A #VALUE etc?

 

[rizzo93]

No, there are no errors.

I don't know if this makes a difference, but that table (q_ApolloProcessed) is the result of using PowerQuery.

I even tried adding back "Application.WorksheetFunction." to that line but no success.

 

[Fluff]

The fact that it's from PQ could make a difference, but as I know nothing about PQ I can't say for certain.
What sort of values are you trying to match & return?

 

[Fluff]

Just realised you need to change b from string to Variant.

 

[rizzo93]

Just realised you need to change b from string to Variant.

I changed b to Variant. While the type mismatch is no longer happening, I'm still not able to pull the value I'm looking for.

As I stepped through the routine, I rolled over "b" and saw "Error 2042" which is the equivalent of "N/A". That's odd though because what I'm looking for exists in the table I'm referring to.

To answer your previous question, I'm using a person's first and last name to return their first initial and last name. So if I'm looking for "Clark Kent", I want "C. Kent" in return.

I appreciate you sticking with me on this. Any other ideas?

 

[Fluff]

Double check that the names are spelt the same & there are no leading/trailing spaces.

 

[rizzo93]

Double check that the names are spelt the same & there are no leading/trailing spaces.

Good call. I check and all looked good.

So I went super simple and worked with this code:

Sub NotAckMe2()
Dim sourceRows As Integer

    Set sourceTbl = Sheets("Apollo Processed").ListObjects("myTable")
    sourceRows = sourceTbl.DataBodyRange.Rows.Count
    MsgBox sourceRows

End Sub

Here's the thing: the code runs fine when I DON'T declare sourceTbl. But when I do (because I think it's good practice, if nothing else), I get a Type mismatch error:

Sub NotAckMe2()
Dim sourceRows As Integer
Dim sourceTbl As ListObjects

    Set sourceTbl = Sheets("Apollo Processed").ListObjects("myTable")
    sourceRows = sourceTbl.DataBodyRange.Rows.Count
    MsgBox sourceRows

End Sub

I changed nothing else but that one line of code. I even tried changing the name of sourceTbl to apples and still got the error when declaring it as a listobject.

I am totally baffled. ???