Results 1 to 2 of 2

Thread: Error 91 Object Variable or With block variable not set
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Error 91 Object Variable or With block variable not set

    I trying to write this macro with the intention of when the command button is selected specified cell data from the current sheet is copy and pasted to the next available row on another sheet. This syntax works fine on a user form but I'm having trouble trying to achieve the same thing with just a work sheet. When I try to run I get error 91. Below is what I've got so far:

    Private Sub CommandButton1_Click()

    Dim lg As Worksheet
    Set lg = Sheets("Log")

    lg.Select
    lg.Range("A2:T2").Select
    Selection.ListObject.ListRows.Add (1)
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With

    lg.Cells(2, 1) = Sheets("sheet2").Range("A1")

    Unload Me
    lg.Select

    End Sub

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,916
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Error 91 Object Variable or With block variable not set

    Two things,
    1. Use the .Value property when populating a cell with another cell's value.
    Code:
    lg.Cells(2, 1) = Sheets("sheet2").Range("A1").Value
    2. You do not need the 'Unload Me' if you are not running this code from a UserForm. It will cause an error.
    Last edited by JLGWhiz; May 18th, 2019 at 04:58 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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