VBA Run-time error '13': Type Mismatch

[willow1985]

Hello, I have this code that I am not sure how it works but it appears to to a certain extent.

What I am looking to do is insert a row at the end of a table. I have other data right below the table starting in cell C22.

This code seems to work, it will insert a row just below A21 without overriding row 22 data. It also brings down all of the formulas in the cells and does not overwrite any data, however I get a mismatch error right before it finishes at line: If col <= UBound(NewData).....

Any help with this code would be greatly appreciated or if there are any other suggestions on how to write this.

Thank you!

Dim lo As ListObject  
  For Each lo In ActiveSheet.ListObjects
  
    lo.AutoFilter.ShowAllData
      Next lo


    Dim sheet As Worksheet
    Dim table As ListObject
    Dim col As Integer
    Dim lastRow As Range


    Set sheet = Range("TAI").Parent
    Set table = sheet.ListObjects.Item("TAI")


    If table.ListRows.Count > 0 Then
        Set lastRow = table.ListRows(table.ListRows.Count).Range
        If Application.CountBlank(lastRow) < lastRow.Columns.Count Then
            table.ListRows.Add
        End If
    End If


 
    If table.ListRows.Count = 0 Then
        table.ListRows.Add Position:=1
        Set lastRow = table.ListRows(1).Range
    Else
        Set lastRow = table.ListRows(table.ListRows.Count).Range
            End If
    For col = 1 To lastRow.Columns.Count
[COLOR=#ff0000]        If col <= UBound(NewData) + 1 Then lastRow.Cells(1, col) = NewData(col - 1)[/COLOR]
[COLOR=#ff0000]    Next col[/COLOR]


End Sub

 

[JoeMo]

Looks like something is missing from the code you posted. Where is the array called NewData declared? If the code does everything you want prior to the For col-Next col block, why not just remove that block?

 

[willow1985]

I think you are right. I was given the code.

I removed the last portion and changed it to the following. Appears to be working now. Thank you very much

Dim lo As ListObject  
  For Each lo In ActiveSheet.ListObjects
  
    lo.AutoFilter.ShowAllData
      Next lo


    Dim sheet As Worksheet
    Dim table As ListObject
    Dim col As Integer
    Dim lastRow As Range


    Set sheet = Range("TAI").Parent
    Set table = sheet.ListObjects.Item("TAI")


    If table.ListRows.Count > 0 Then
        Set lastRow = table.ListRows(table.ListRows.Count).Range
        For col = 1 To lastRow.Columns.Count
            If Trim(CStr(lastRow.Cells(1, col).Value)) <> "" Then
                table.ListRows.Add
                Exit For
            End If
        Next col
    Else
        table.ListRows.Add
        End If




End Sub

 

[JoeMo]

I think you are right. I was given the code.

I removed the last portion and changed it to the following. Appears to be working now. Thank you very much

You are welcome - thanks for the reply.