Type mismatch error when trying to select table data

Question

Hi,

Something has been driving me crazy for the past umpteen hours, which I can't seem to wrap my mind around: I'm trying to select the DataBodyRange of a column in a table through VBA, and I get a "Type Mismatch" (error 13) with a line such as:

Sheet4.ListObjects("InspectionsPassees").ListColumns("Nom").DataBodyRange

I've checked and rechecked the names of the table and column, no problem there. I initially had this long Xlookup formula that threw the Type Mismatch error, so I started breaking it down into smaller pieces, and realized that the line above was the problem. By the way, same error with:

Sheet4.ListObjects("InspectionsPassees").ListColumns("Nom").Range

But:

MsgBox(Sheet4.ListObjects("InspectionsPassees").ListColumns("Nom").Range.Count)

will happily display "66" without a problem.

I don't get it...

Any help will be greatly appreciated!

NB: The Xlookup snippet that threw the error looked like that:

Dim ip As ListObject
Dim nom As Range, prenom As Range, nomJF As Range, DOB As Range, inspP As Range
Set ip = Sheet4.ListObjects("InspectionsPassees")
Set nom = ip.ListColumns("Nom").DataBodyRange
Set prenom = ip.ListColumns("Prenom").DataBodyRange
Set nomJF = ip.ListColumns("Nom_JF").DataBodyRange
Set DOB = ip.ListColumns("Date_naissance").DataBodyRange
Set inspP = ip.ListColumns("À revoir").DataBodyRange
With Sheet3.ListObjects("Contractuels")

    For Each Cx In .ListRows
        If Application.Intersect(Cx.Range, .ListColumns("Cx").Range) = "C0" Then
            Application.Intersect(Cx.Range, .ListColumns("Insp").Range) = "VC"
        ElseIf Application.XLookup(1, nom = Application.Intersect(Cx.Range, .ListColumns("Nom").Range), inspP) = "oui" Then
            Application.Intersect(Cx.Range, .ListColumns("Insp").Range) = "INSP"
        Else: Application.Intersect(Cx.Range, .ListColumns("Insp").Range) = "non"

        End If

    Next Cx

Knowing that the point was to use boolean logic and multiply the criteria in the XLOOKUP formula (hence the definitions for all the columns, but I shortened it to analyzing one column for the sake of example...)

Best,

Ari ;o)

Answer 1

Hi, AriBouaniche

We understand the issue you are experiencing. Unfortunately,  VBA is not supported on the Microsoft Answers forum. It is more suitable for publishing on Stack Overflow    

Post VBA programming questions to Stack Overflow by using the vba tag, along with any other relevant tags. 

Please note that Stack Overflow has guidelines such as requiring a descriptive title, a complete and concise problem statement, and sufficient details to reproduce your issue. Feature requests or overly broad questions are considered off-topic.

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology. 

I won't be able to help you, but I'll leave that question open in case one of our amazing volunteers has ideas for you.

Best Regards, 

Mila.L-MSFT| Microsoft Community Support Specialist