MatchCase:=False).row and "run-time error 91"

Question

I want to enter in some text and values in a commandbutton and have it populate a spreadsheet. my code is here:

Private Sub CommandButton1_Click()

Dim rng As Range
Dim LastRow As Long

Set rng = ActiveSheet.ListObjects("table1").Range


LastRow = rng.Find(What:=” * ”, _
    After:=rng.Cells(1), _
    Lookat:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row

    rng.Parent.Cells(LastRow + 1, 1).Value = TextBox1.Text
    rng.Parent.Cells(LastRow + 1, 2).Value = ComboBox1.Text
    rng.Parent.Cells(LastRow + 1, 3).Value = TextBox3.Value
    rng.Parent.Cells(LastRow + 1, 4).Value = TextBox4.Value
    rng.Parent.Cells(LastRow + 1, 5).Value = TextBox5.Value

End Sub

I keep getting run-time error 91 on matchcase:=false).row and I don't know why. An extra set of eyes would be so helpful.

Answer 1

It looks like you're adding a row to an existing table (ListObject). There's no need to find the last row - you can just use ListRows.Add. If you don't specify the position you want the row to be added at, it is automatically added at the bottom.

This simplifies what you are trying to do.

Private Sub CommandButton1_Click()
    Dim newRow As ListRow
    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("table1")
    Set newRow = tbl.ListRows.Add

    With newRow
        .Range(1) = Textbox1.Text
        .Range(2) = ComboBox1.Text
        .Range(3) = TextBox3.Value
        .Range(4) = TextBox4.Value
        .Range(5) = TextBox5.Value
    End With
End Sub