Unknown problem with adding a new row to a table

[Burrgogi]

I have a macro works roughly 70% of the time.

Dim Tbl1 As ListObject
Dim KeyWord As String
Set Tbl1 = ThisWorkbook.Worksheets("Sheet3").ListObjects("My_Table")
    With Tbl1
        'shift from an extra row if list has header
        Dim iHeader As Long
        If .Sort.Header = xlYes Then
            iHeader = 1
        Else
            iHeader = 0
        End If
       'find the last row of the list
        tLastRow = Tbl1.ListRows.Count + 1
    'add the data a row after the end of the list
        Tbl1.Range(tLastRow + iHeader, "A").Value = KeyWord
        Tbl1.Range(tLastRow + iHeader, "B").FormulaR1C1 = "=VLOOKUP(RC1,'Complete Set'!R2C1:R2617C3,3,FALSE)"
    End With

Occasionally it throws up an error on the very last line just before End With. Initially I had it written as .Formula, then I realized I needed .FormulaR1C1. I thought I fixed it for good but it still pops up with an error occasionally.

Column B is not part of the table. I beginning to suspect maybe that's why it's causing the error. I don't know why it works sometimes and not others.

 

[Alex Blakenburg]

Seems an unusual approach. Why is Column B not part of the table ?
If it was part of the table the formula could be pre-populated and would automatically fill down as you add more records.

Also can you show us an image of the table including the table headings and including the row and column references.
What error message to you get ?

I assume you are only showing part of the code since KeyWord is not being assigned a value.

 

[Burrgogi]

Hi Alex,

I know it's a little unusual approach. The table was created long before I ever created the macro. Initially both columns A & B were part of the table and later, I decided the table did not need column B, then afterwards I created the macro. I can't remember what the error message was saying exactly but every time it tripped, VBE would always highlight that line yellow. (line containing the VLOOKUP formula).

Seems an unusual approach. Why is Column B not part of the table ?
If it was part of the table the formula could be pre-populated and would automatically fill down as you add more records.

Yeah, I thought that is the way tables should work, but it was happening for some reason. I thought perhaps my version of Excel doesn't do that? (Excel 2010) It was late at night when I was working on this project and was not thinking very clearly. When I saw your response, I deleted the worksheet in question and recreated it from scratch. Just for the sake of simplicity, the (new) table includes both columns A & B.

Now with the new sheet and new table, things are working smoothly. (For now anyway). When I add a new word to the end of the table, the VLOOKUP formula gets automatically populated in col. B just like it should. I suspect there was something corrupted in the original sheet. (I had an old backup copy so I did not need to reenter the data over again).

I also modified my code slightly. After some thought, I realized declaring "Tbl1" at the top of my macro was unnecessary. It's not like I"m doing something anything fancy with it. This is the modified section now:

    With ThisWorkbook.Worksheets("Sheet3").ListObjects("new_table")
        'shift from an extra row if list has header
        Dim iHeader As Long
        If .Sort.Header = xlYes Then
            iHeader = 1
        Else
            iHeader = 0
        End If
       'find the last row of the list
        tLastRow = .ListRows.Count + 1
    'add the data a row after the end of the list
        .Range(tLastRow + iHeader, "A").Value = KeyWord
        '.Range(tLastRow + iHeader, "B").FormulaR1C1 = "=VLOOKUP(RC1,'Complete Set'!R2C1:R2617C3,3,FALSE)"
    End With

I also declared tLastRow as Long instead of Range since it's returning a number rather a cell address.

I assume you are only showing part of the code since KeyWord is not being assigned a value.

It is assigned a value but it always varies day to day. The macro looks at the very last entry in Sheet 1, col. A and whatever is entered there is = KeyWord

 

[Alex Blakenburg]

Most people would just assume that the headers are always going to be turned on. Your current .Sort.Headers line is always evaluating as True, if you do want to check for the heading being turned on make the replacement shown below.

        'If .Sort.Header = xlYes Then
        If .ShowHeaders Then

 

[Burrgogi]

Most people would just assume that the headers are always going to be turned on. Your current .Sort.Headers line is always evaluating as True, if you do want to check for the heading being turned on make the replacement shown below.

        'If .Sort.Header = xlYes Then
        If .ShowHeaders Then

OK thanks. I've made the change as suggested.