Run-time error 91 Object variable or With block variable not set

[SKEEEETER]

Hi I have a sheet that certain input is added which then has formulas to calculate certain things.

I want to when click a button that it adds the contents of certain cells on the sheet into another table on another sheet. Once that has completed to then clear all the data that the user has imputed onto form.

Private Sub CommandButton1_Click()

Dim tbl As ListObject
Dim ws As Worksheet
Dim lrow As Range
Dim lrow2 As Long


Set tbl = Sheets("PRP History").ListObjects("tblPRP")

    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
    End If

    lrow2 = tbl.ListRows.Count

      tbl.DataBodyRange(lrow2, 1).Value = Range("B1").Value

      tbl.DataBodyRange(lrow2, 2).Value = Range("F2").Value

      tbl.DataBodyRange(lrow2, 3).Value = Range("B2").Value
     
      tbl.DataBodyRange(lrow2, 4).Value = Range("N4").Value
     
      tbl.DataBodyRange(lrow2, 5).Value = Range("O4").Value
     
      tbl.DataBodyRange(lrow2, 6).Value = Range("N5").Value
     
      tbl.DataBodyRange(lrow2, 7).Value = Range("O5").Value
     
      tbl.DataBodyRange(lrow2, 8).Value = Range("N6").Value
     
      tbl.DataBodyRange(lrow2, 9).Value = Range("O6").Value
     
      tbl.DataBodyRange(lrow2, 10).Value = Range("N7").Value
     
      tbl.DataBodyRange(lrow2, 11).Value = Range("O7").Value
     
      tbl.DataBodyRange(lrow2, 12).Value = Range("N9").Value
     
      tbl.DataBodyRange(lrow2, 13).Value = Range("O9").Value
     
      tbl.DataBodyRange(lrow2, 14).Value = Range("N10").Value
     
      tbl.DataBodyRange(lrow2, 15).Value = Range("O10").Value
     
ActiveSheet.Protect
On Error Resume Next
ActiveSheet.UsedRange = ""
On Error GoTo 0
ActiveSheet.Unprotect
ActiveSheet.Protect

     


End Sub

I did have it all working but was cleaning up some things that and now get Run-time error 91 Object variable or With block variable not set.

I am hoping someone can point me in the right direction.

[Andy Pope]

The problem arises when listrows.count IS zero. It assign the value zero to lrow2, which causes an error when used as the row reference to DataBodyRange.

You need an else condition to perform some action when the listcount is zero.

[SKEEEETER]

Hi Andy thanks for your reply, I know something simple.

Where exactly would I put it?

[Andy Pope]

Not sure what you want to do if no rows currently exist, although I guess it would be to add a row and assign the count again to lrow2

    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
    Else
        ' do something here if zero rows.
    End If

[SKEEEETER]

Yes I have used the code before, it adds a row in table then adds the values into the table.

I have tried what you have suggested but still same error.

I have found that as had no data in table throws this error , as soon as there is data in the first row works ok.

[Andy Pope]

This change worked for me, but I don't know whether simply adding a list row is what your program requires.

    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
    Else
        tbl.ListRows.Add
    End If