Runtime error 91 Object Variable or with block variable not set

Pookiemeister

Board Regular
Joined
Nov 26, 2015
Messages
209
Office Version
2010
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:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,718
Office Version
365
Platform
Windows
what type of value is being searched for ?
- string ?
- numeric ?
- boolean ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
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
 

Pookiemeister

Board Regular
Joined
Nov 26, 2015
Messages
209
Office Version
2010
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.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,718
Office Version
365
Platform
Windows
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:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,364
Office Version
365
Platform
Windows
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,718
Office Version
365
Platform
Windows
@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)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,364
Office Version
365
Platform
Windows
Oops, didn't spot the different sheet names. :eek:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
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.
 

Pookiemeister

Board Regular
Joined
Nov 26, 2015
Messages
209
Office Version
2010
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:

Forum statistics

Threads
1,082,604
Messages
5,366,581
Members
400,904
Latest member
ndaines meriabi

Some videos you may like

This Week's Hot Topics

Top