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]

I notice that you have now amended the sheet name from SD to SD_Employee_List when populating the combobox
So the sheet names (for populating and searching) should be consistent
In which case @Norie please can you PM me next weeks lottery numbers

Norie's method is therefore the simplest approach
- there no requirement to search
- you simply use the selected value's position in the list to determine the row number in the worksheet

Code below works for me and populates the label wth the correct value from column B

Private Sub UserForm_Initialize()
    Dim rngNames As Range
    With Sheets("SD_Employee_List")
        Set rngNames = .Range("A3", .Range("A" & .Rows.Count).End(xlUp))
    End With
    On Error Resume Next
    DTPicker1.Value = Date
    lblWotM.Caption = WeekOfMonth(CDate(DTPicker1))
    UserForm1.ComboBox1.List = rngNames.Value
    Me.Label1.Enabled = False
End Sub


Private Sub ComboBox1_Change()
    Dim idx As Long
    idx = Me.ComboBox1.ListIndex
    If idx <> -1 Then Label1 = Sheets("SD_Employee_List").Range("B" & idx + 3)
End Sub

 

[Yongle]

OR...

Load both columns into the Combobox (display either or both columns in userfom) and use the value in the 2nd column to populate the label
(zero based so Column(0) is the first column)

Private Sub UserForm_Initialize()
    Dim rngNames As Range
    With Sheets("SD_Employee_List")
        Set rngNames = .Range("A3", .Range("A" & .Rows.Count).End(xlUp))[COLOR=#ff0000].Resize(, 2)[/COLOR]
    End With
    On Error Resume Next
    DTPicker1.Value = Date
    lblWotM.Caption = WeekOfMonth(CDate(DTPicker1))
    UserForm1.ComboBox1.List = rngNames.Value
    Me.Label1.Enabled = False
End Sub


Private Sub ComboBox1_Change()
    If Me.ComboBox1.ListIndex - 1 Then Label1 = ComboBox1.Column(1)
End Sub

 

[Pookiemeister]

Thank You all for your wonderful help. I greatly appreciate it. Just a quick question, how did you get to this VBA knowledge level? Was it books read, trial an error. Any advice would be greatly appreciated. Thank you again.

 

[Yongle]

how did you get to this VBA knowledge level?

In my case

- a lot of trial and error

- google can help as a good starting point - but NEVER simply copy and use code found (after initial testing to confirm it works) without deconstructing it and reconstructing it from the ground up

- to ensure personal development extends beyond the immediate issue, when solving a problem look at other attributes available within any functions used etc - anything that may be useful in future - there is a Microsoft page for virtually everything (example : https://docs.microsoft.com/en-us/office/vba/api/excel.range.find )

- subscribe to various newsletters and READ them when they hit inbox!
https://powerspreadsheets.com/
https://excelribbon.tips.net/index.html
https://excelmacromastery.com/
https://www.myonlinetraininghub.com/

 

[Fluff]

how did you get to this VBA knowledge level?

For me it was primarily from helping people here.
You get to see all sorts of problems that you may not come across in you rline of work.
Also you get to see how other people resolve those problems.

 

[Pookiemeister]

Thank You so very much for your guidance on becoming better at VBA. I will definitely try to all your ideas. Thank You again.

 

[Fluff]

You're welcome & thanks for the feedback