Results 1 to 6 of 6

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

Threaded View

  1. #1
    Registered User
    Join Date
    09-26-2015
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    79

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

    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.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Run-time error '91': Object variable or With block variable not set when closing userform
    By bishoposiris in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-10-2016, 08:59 AM
  2. [SOLVED] Run-time error 91: Object variable or With block variable not set
    By onmyway in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-10-2015, 09:29 AM
  3. Replies: 0
    Last Post: 04-16-2013, 07:15 AM
  4. Run-time error 91 - Object variable or with block variable not set
    By twckfa16 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-31-2012, 06:08 PM
  5. Replies: 6
    Last Post: 12-21-2012, 08:03 AM
  6. Replies: 1
    Last Post: 09-25-2012, 08:03 PM
  7. [SOLVED] Intermittent Run-time Error 91: Object Variable or With Block variable not set
    By fraanchtoast in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-29-2012, 10:11 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1