Runtime error 91 Object Variable or with block variable not set

Pookiemeister

Well-known Member
Joined
Jan 6, 2012
Messages
563
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
what type of value is being searched for ?
- string ?
- numeric ?
- boolean ?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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")
    [COLOR=#ff0000]On Error Resume Next [/COLOR]                              [COLOR=#006400]'prevents code failing[/COLOR]
    Set rEmpIniValue = Rng.Find(what:=Me.ComboBox1.Value, lookat:=xlWhole, LookIn:=xlValues)
    [COLOR=#ff0000]If Not[/COLOR] rEmpIniValue [COLOR=#ff0000]Is Nothing[/COLOR] 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, [COLOR=#ff0000]MatchCase:=False[/COLOR], 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:
Upvote 0
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
 
Upvote 0
@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)
 
Upvote 0
Oops, didn't spot the different sheet names. :eek:
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top