Run Time Error '91' When adding userform data to table

Question

I'm trying to take simple data entered into two text boxes within a userform and then have this data added to a table named "AvantAct". I want the data entered into the first blank row in the table every time the userform is run. Interestingly, the first time I did this it worked flawlessly. However after exiting the workbook and then coming back to it later I seem to get: -

Run time error '91': Object variable or With block variable not set.

When I debug the following line is highlighted:

tbl.DataBodyRange(lrow2, 1). Value = Me.TxtDate.Value

I should add that my table is currently an empty (freshly inserted) table. It has 8 columns (with headers), one empty row (from being inserted) and a total row.

Any suggestions?

Private Sub SubmitButton_Click()

Dim ws As Worksheet
Dim tbl As ListObject
Dim TxtDate As Date
Dim TxtPmt As Currency
Dim col As Integer
Dim lrow As Range
Dim lrow2 As Long


Set ws = ActiveWorkbook.Worksheets("Avant")
Set tbl = ws.ListObjects.Item("AvantAct")

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

lrow2 = tbl.ListRows.Count
tbl.DataBodyRange(lrow2, 1).Value = Me.TxtDate.Value
tbl.DataBodyRange(lrow2, 3).Value = Me.TxtPmt.Value

Unload Me

End Sub

Answer 1

The issue comes from having an empty table to begin with.

If tbl.ListRows.Count > 0 Then
    Set lrow = tbl.ListRows(tbl.ListRows.Count).Range
    ...
End If

Because the count was not greater than zero (it was zero) lrow was never set, hence the error.

If tbl.ListRows.Count = 0 Then
    tbl.ListRows.Add
else
    Set lrow = tbl.ListRows(tbl.ListRows.Count).Range
    ...
End If

In addition, your question asks: -

I want the data entered into the first blank row

The code is not doing this, the code is checking only the last row, and adding a row if it wasn't already empty, so in a list where out of 5 rows, the third row was empty, the third row would not be used but a row at the bottom would be added instead. The below would do as you expect: -

Private Sub SubmitButton_Click()
Dim ws          As Worksheet
Dim tbl         As ListObject
Dim TxtDate     As Date
Dim TxtPmt      As Currency
Dim col         As Integer
Dim lrow        As Range
Dim lrow2       As Long
Dim BlnYesNo    As Boolean

Set ws = ActiveWorkbook.Worksheets("Avant")
Set tbl = ws.ListObjects.Item("AvantAct")

If tbl.ListRows.Count = 0 Then
    tbl.ListRows.Add
    lrow2 = 1
Else
    For lrow2 = 1 To tbl.ListRows.Count
        Set lrow = tbl.ListRows(lrow2).Range

            'If it stays true, we must add a row
            BlnYesNo = True

            For col = 1 To lrow.Columns.Count
                If Trim(CStr(lrow.Cells(1, col).Value)) <> "" Then
                    BlnYesNo = False
                    Exit For
                End If
            Next

            If BlnYesNo Then Exit For

        Set lrow = Nothing
    Next

    'If its false then all rows had data and we need to add a row
    If Not BlnYesNo Then
        tbl.ListRows.Add
        lrow2 = tbl.ListRows.Count
    End If

End If

tbl.DataBodyRange(lrow2, 1).Value = "A"
tbl.DataBodyRange(lrow2, 3).Value = "B"

Set tbl = Nothing
Set ws = Nothing

End Sub