VBA Run-time error '13': Type Mismatch

willow1985

Active Member
Joined
Jul 24, 2019
Messages
316
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!

Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,645
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

Active Member
Joined
Jul 24, 2019
Messages
316
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 :)

Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,645
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.
 

Forum statistics

Threads
1,078,367
Messages
5,339,783
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top