Runtime error 91 Object Variable or with block variable not set

[Pookiemeister]

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

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

 

[Yongle]

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

 

[Fluff]

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]

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.

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]

Try this which handles VBA not finding ComboBox1's value in column A in SD_Employee_List

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:

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.

 

[Norie]

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.

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]

@Norie
Is that correct here ?

The list is being populated from Sheet named SD

UserForm1.ComboBox1.RowSource = "SD!A3:A" & Range("A" & Rows.Count).End(xlUp).Row

But Range.Find is looking at SD_Employee_List

Worksheets("SD_Employee_List").Activate
    With ActiveSheet.Range("A:A")
        Set rEmpIniValue = .Find(what:=Me.ComboBox1.Value, lookat:=xlWhole, LookIn:=xlValues)

 

[Norie]

Oops, didn't spot the different sheet names.

 

[Fluff]

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]

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.

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

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