Search code examples
excelvbalistobject

Subscript out of range / listobject exists but not found

I want to automatically add a row if the last row is filled of my listobject. But the listobject is not identified on the if statement, and when I set variable tbl to the listobject it says the subscript is out of range.

With Sheets("Ruimtelijst")
    lastRow = .Cells(Cells.Rows.Count, "G").End(xlUp).Row
End With
    For Row= 4 To lastRow
        With Sheets("Uitwendige scheidingen")
'Intersect is not working either.
            'If Not Intersect(Target, .ListObjects("Table_" & Row - 3)) Is Nothing Then
            Set tbl = .ListObjects("Table_" & Row - 3)
            'End If
         End With
    Next

code to add the listrows:(in another module)

Set tbl= .ListObjects.Add(xlSrcRange, Source:=.Range("F" & NextRow + 11 & ":G" & NextRow + 11), XlListObjectHasHeaders:=xlYes)
tbl.Name = "Table " & Rij - 3

and as you can see. the listobject is added in excel:(GrondWand is the original table name, changed it to english for here)

Listobject shown in excel

I've tried:

  • changing Sheets("Uitwendige scheidingen") to Sheets(Sheet2) (apparently Sheet2 doesn't exist)

  • Sheet2.ListObjects("table_1") instead of Sheet2.ListObjects("Table_" Row - 3)

  • Copied the table name in excel and pasted it in the Set tbl = .ListObjects("Table_" & Row - 3) line

Solution

  • maybe you can adjust this to your needs? Again, I'm unsure what exactly you want so I wrote it as generic as I could. 

    
Sub test()

    'Goes through all sheets
For i = 1 To ThisWorkbook.Sheets.Count
        'And through all listobjects on those sheets.
    For j = 1 To ThisWorkbook.Sheets(i).ListObjects.Count
            'Just for your info: to see names and references?
        Debug.Print "Sheet, Listobject number and its name: " & i & " " & j & " " & ThisWorkbook.Sheets(i).ListObjects(j).Name
            'Find the last row
        LastRowOfThisListObject = ThisWorkbook.Sheets(i).ListObjects(j).DataBodyRange.Rows.Count
            'If that row (first column) is not empty then
        If Not ThisWorkbook.Sheets(i).ListObjects(j).Range(LastRowOfThisListObject, 1) Like "" Then
                'Add another empty row below
            ThisWorkbook.Sheets(i).ListObjects(j).ListRows.Add

                'If you also want to write to it go with this:
'            With ThisWorkbook.Sheets(i).ListObjects(j).ListRows.Add
'                .Range.ClearFormats
'                .Range(1, 1) = " "
'                .Range(1, 2) = " "
'                .Range(1, 3) = " "
'                etc.
'                Call OtherSub(.Range) '...for example
'            End With

        End If
    Next
Next

End Sub

    Edit to answer a follow up. This works for me:

    Sub test2()

Set target = ThisWorkbook.Sheets(1).Range("A1:D5")
'The table is in Range("B4:I15")
Row = 4

With Sheets(1)
If Not Intersect(target, .ListObjects("Tabelle" & Row - 3).Range) Is Nothing Then
    Debug.Print "test"
End If
End With

End Sub

    PS: its really difficult to guess where you defined what with the limited code you showed. I just made a table and defined some range to test it. I think your error is the missing .range. Hope you can adjust this as needed