Warm tip: This article is reproduced from stackoverflow.com, please click
excel vba listobject

How to fix error 438 when adding listobjects?

发布于 2020-03-29 12:48:24

I have a problem with VBA. I am fairly new to it so some help would be appreciated :)

What I want to achieve:

If a child table has a row filled, i want it to add a new row.

What I have tried

because i have 10 parent tables underneath eachother I cannot use Cells(Cells.Rows.Count, "E").End(xlUp).Row because it will only count the last parent table

So I tried doing it with listobjects, But whenever i try to add a listobject:

With Worksheets("Uitwendige scheidingen")
.ListObject.Add(Range("F" & NextRow + 25)).Name = "tbl_schuindak_orientatie" & Rij
End With

(Rij = the parent table) (NextRow = start position of the parent table)

I get the error 438

Note

I do not have any listobjects added in the sheet before. so the parent tables are basicly 'fake' tables and not listobjects

Questioner
Nemoko
Viewed
22
JvdV 2020-01-31 18:25

First of, you have to .Add a ListObject to the ListObjects collection. Furthermore, you can't set a new ListObject through it's Name property. You first have to initialize it with a proper source range. Then rename it after. As a simplistic example:

enter image description here

Sub Test()

Dim nxt As Long
Dim tbl As ListObject

With ThisWorkbook.Worksheets("Sheet1")
    For x = 1 To 10 Step 3
        Set tbl = .ListObjects.Add(xlSrcRange, Source:=.Range(.Cells(x, 1), .Cells(x + 1, 1)), XlListObjectHasHeaders:=xlYes)
        tbl.Name = "Table" & x
    Next x
End With

End Sub

enter image description here

Now apply this logic to your situation.

Note: See how xlYes is applied to tell the XlListObjectHasHeaders parameter in this example it needs to take into consideration my source ranges have headers. Set to xlNo if yours don't.