Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Runtime error 91 Object Variable or with block variable not set
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2015
    Posts
    190
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Runtime error 91 Object Variable or with block variable not set

    Some times when I step through or run the code below it works but majority of the time, I will the error message stated in the title. When I make a selection with the combobox and watch the code, there is nothing set in the rEmpIniValue and rEmpIniValue.select turns yellow. But there is a value in the combobox. So why does this code some times work and other times it doesn't?

    Thank You

    Code:
    Private Sub ComboBox1_Change()    
        Dim rEmpIniValue As Range
        
        Worksheets("SD_Employee_List").Activate
        
        With ActiveSheet.Range("A:A")
            Set rEmpIniValue = .Find(what:=Me.ComboBox1.Value, lookat:=xlWhole, LookIn:=xlValues)
            rEmpIniValue.Select
            Label1.Caption = ActiveCell.Offset(, 1)
        End With
    End Sub
    Last edited by Pookiemeister; Oct 15th, 2019 at 05:59 AM.

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,059
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Runtime error 91 Object Variable or with block variable not set

    what type of value is being searched for ?
    - string ?
    - numeric ?
    - boolean ?

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Runtime error 91 Object Variable or with block variable not set

    Excel remembers your settings for Find, so if you are searching for text, it may be doing a case sensitive search when it should be case insensitive
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Nov 2015
    Posts
    190
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 91 Object Variable or with block variable not set

    First thank you both for the quick response. Secondly, perhaps a better explanation is needed, my apologizes on not giving one. On my a spreadsheet named "SD_Employee_List" there is list of employee names in column A and in column B is a list of their initials. As of now, on my form, I have a combobox and a label. The combobox is populated with the values of column A (employee names). Once the name is found using the find function, it will offset one column to the right and display that employees initials in label1. The combobox is populated when the form is initialized. Here is the code for that below.
    Code:
    Private Sub UserForm_Initialize()    Label6.Caption = "Select the production line being evaluated"
        UserForm1.ComboBox1.RowSource = "SD!A3:A" & Range("A" & Rows.Count).End(xlUp).Row
        DTPicker1.Value = Date
        lblWotM.Caption = WeekOfMonth(CDate(DTPicker1))
    End Sub
    Thank You again.

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,059
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Runtime error 91 Object Variable or with block variable not set

    Try this which handles VBA not finding ComboBox1's value in column A in SD_Employee_List
    Code:
    Private Sub ComboBox1_Change()
        Dim rEmpIniValue As Range, Rng As Range
        Set Rng = Worksheets("SD_Employee_List").Range("A:A")
        On Error Resume Next                               'prevents code failing
        Set rEmpIniValue = Rng.Find(what:=Me.ComboBox1.Value, lookat:=xlWhole, LookIn:=xlValues)
        If Not rEmpIniValue Is Nothing Then
            Label1.Caption = rEmpIniValue.Offset(, 1)
        Else
            MsgBox Me.ComboBox1.Value, vbExclamation, "NOT FOUND:"
        End If
    End Sub
    Excel remembers your settings for Find, so if you are searching for text, it may be doing a case sensitive search when it should be case insensitive
    further mod to the code that would deal with @Fluff suggestion:
    Code:
    Set rEmpIniValue = Rng.Find(what:=Me.ComboBox1.Value, MatchCase:=False, lookat:=xlWhole, LookIn:=xlValues)
    If the value in combobox1 is not being found and you are convinced that it is there to be found, then come back and we'll help you puzzle out why that is happening.
    Last edited by Yongle; Oct 16th, 2019 at 01:14 AM.

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,191
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 91 Object Variable or with block variable not set

    If you are populating the combobox directly from column A there's no need to use Find to locate the selected value, you can use the ListIndex property to get the row.
    Code:
    Private Sub ComboBox1_Change()    
    Dim rEmpIniValue As Range
    Dim idx As Long
    
        idx = Me.ComboBox1.ListIndex
    
        If idx <> -1 Then 
            Set rEmpIniValue = Worksheets("SD_Employee_List").Range("A" & idx+3)
            Me.Label1.Caption = rEmpIniValue.Offset(, 1).Value
        End If
    
    End Sub
    If posting code please use code tags.

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,059
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Runtime error 91 Object Variable or with block variable not set

    @Norie
    Is that correct here ?

    The list is being populated from Sheet named SD
    Code:
    UserForm1.ComboBox1.RowSource = "SD!A3:A" & Range("A" & Rows.Count).End(xlUp).Row
    But Range.Find is looking at SD_Employee_List
    Code:
    Worksheets("SD_Employee_List").Activate
        With ActiveSheet.Range("A:A")
            Set rEmpIniValue = .Find(what:=Me.ComboBox1.Value, lookat:=xlWhole, LookIn:=xlValues)

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,191
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 91 Object Variable or with block variable not set

    Oops, didn't spot the different sheet names.
    If posting code please use code tags.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Runtime error 91 Object Variable or with block variable not set

    Quote Originally Posted by Yongle View Post
    The list is being populated from Sheet named SD

    But Range.Find is looking at SD_Employee_List
    Maybe that's why nothing is being found. Is the code actually looking at the right sheet.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Nov 2015
    Posts
    190
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 91 Object Variable or with block variable not set

    Thank you all for your help. I tried Yongle's code and it worked sometimes. When I occasionally reran the code, the combobox would only display the first 8 out of the 50 people on the spreadsheet. When I would try it again it would display all 50 names and then other times only the first eight people again. Not sure what I might of done wrong? So I am redisplaying the code that I have. As always thank you for all your help.
    Code:
    Private Sub ComboBox1_Change()    Dim rEmpIniValue As Range, Rng As Range
        Set Rng = Worksheets("SD_Employee_List").Range("A:A")
        On Error Resume Next                               
        Set rEmpIniValue = Rng.Find(what:=Me.ComboBox1.Value, lookat:=xlWhole, LookIn:=xlValues)
        If Not rEmpIniValue Is Nothing Then
            Me.Label1 = rEmpIniValue.Offset(, 1)
        Else
            MsgBox Me.ComboBox1.Value, vbExclamation, "NOT FOUND:"
        End If
            
    End Sub
    Code:
    Private Sub UserForm_Initialize()    
    On Error Resume Next
        DTPicker1.Value = Date
        lblWotM.Caption = WeekOfMonth(CDate(DTPicker1))
        UserForm1.ComboBox1.RowSource = "SD_Employee_List!A3:A" & Range("A" & Rows.Count).End(xlUp).Row
        Me.Label1.Enabled = False
    End Sub
    Last edited by Pookiemeister; Oct 16th, 2019 at 09:40 PM.

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
  •