Runtime error #91, Object variable or with block variable not set

[Vivek Roshan]

hi Mr. Fluff

I have added search command button to userform in excel VBA and wanted to search the data through the dates displayed in combobox. The idea is to search date in worksheet called ICC_Data and if it finds, then it will display the data in TextBox1, TextBox2 and TextBox3 of userform. When I am selecting the date in combobox and pressing search command button. First, it coverts the date into 5 digit value and then shows an error as:

Runtime error 91, object variable or with block variable is not set.


My code is given below and in my Workbook Sheet2 is named as ICC_Data.
-------------------------------------------------------------------------------------
Private Sub CmdSearch_Click()

Dim Findvalue As Range


Set Findvalue = Sheet2.Range("A:A").Find(what:=Me.ComboBox1.Value, LookIn:=xlFormulas, lookat:=xlWhole)

Me.TextBox1.Value = Findvalue.Value
Me.TextBox2.Value = Findvalue.Offset(0, 1).Value
Me.TextBox3.Value = Findvalue.Offset(0, 2).Value


End sub
---------------------------------------------------------------------------------------------------------

Can you please help me to resolve this problem.

Regards,

 

[pike]

Hello,
you will have an error if nothing is found use,,

    Dim Findvalue As Range
    Set Findvalue = Range("A:A").Find(what:=Me.ComboBox1.Value, LookIn:=xlFormulas, lookat:=xlWhole)
    If Not Findvalue Is Nothing Then
        Me.TextBox1.Value = Findvalue.Value
        Me.TextBox2.Value = Findvalue.Offset(0, 1).Value
        Me.TextBox3.Value = Findvalue.Offset(0, 2).Value
    End If

 

[pvr928]

Hi Vevek Roshan

You'll have to show exactly on what line the error is occurring - I'm not getting the same error as you (rather errors particular to the fact I don't have UserForms in the workbook in which I'm testing your code).

Step through your code using F8 (or otherwise run it) and let us know which line it stops at.

Cheers

pvr928

 

[Vivek Roshan]

Hi pvr,

I am getting error on the highlighted code:

-----------------------------------
Private Sub CmdSearch_Click()

Dim Findvalue As Range

Set Findvalue = Sheet2.Range("A:A").Find(what:=Me.ComboBox1.Value, LookIn:=xlFormulas, lookat:=xlWhole)

Me.TextBox1.Value = Findvalue.Value

Me.TextBox2.Value = Findvalue.Offset(0, 1).Value
Me.TextBox3.Value = Findvalue.Offset(0, 2).Value

End sub

 

[Vivek Roshan]

Hi Pike,

I have tried your code. It returns only 5 digit figure in combobox itself. Data is not transferred to respective text boxes in userform. I do not see in your code the worksheet reference. Actually, I am having a userform in Sheet1 which has combobox with search button and with text boxes. Combobox gets listed automatically with dates that I enter through TextBox1. Now I want to search with dates in the combobox. If I click on the date, it will show the data in TextBox1, TextBox2 and TextBox3. This is what exactly I want. For you to help me, I want to mention that my data in Sheet2 named as ICC_Data.

Please see if you can help me. Will be highly appreciated.

 

[pike]

when using the Find Method to Find Dates things can become somewhat tricky

Option Explicit

Private Sub CommandButton1_Click()
    Dim Findvalue As Range
    Dim strDate As String

    strDate = Format(CDate(Me.ComboBox1.Value), "Short Date")
    Set Findvalue = Sheet2.Range("A:A").Find(What:=CDate(strDate), LookIn:=xlFormulas, LookAt:=xlWhole)
    If Not Findvalue Is Nothing Then
        Me.TextBox1.Value = Findvalue.Value
        Me.TextBox2.Value = Findvalue.Offset(0, 1).Value
        Me.TextBox3.Value = Findvalue.Offset(0, 2).Value
    End If
End Sub

 

[Vivek Roshan]

Hello Pike,

Thanks a lot for your coding which worked magically. But I face a small problem. When search button is pressed with the selected date in combobox list, the date gets changed to 5 digit number. However, the display of all relevant data in respective textboxes is ok. Any suggestion on this issue will be appreciated.

Thanks once again for your valuable help.

 

[pike]

hello,
that's weird, how do you populate the combobox?