VBA Run-time error '13': Type Mismatch

willow1985

Board Regular
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
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

Board Regular
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
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top