VBA: I keep getting an error related to object variable not set properly-CodePudding

I am trying to set this simple code but no matter what I do, I always get an error 'object required' on the line:

Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues)

My source code is as follows:

Option Explicit
'I'm going to let the program search for the date in the pre-filled date -> select the entire row & column below -> move it down by 1 row -> insert the date above the moved pre-filled date -> fill other details
Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim submittedDate As Date
    Dim searchValue As Variant
    Dim searchRange As Variant
    Dim foundCell As Range
    
    Set ws = Sheet1 'setting it up so that I'm dealing with Sheet1
    ws.Range("A7:A37").NumberFormat = "dd-mmm" 'setting the number format of the values in A7:A37 as dd-mmm
    submittedDate = Me.TextBox1.Value 'setting the input value as a variable
    searchValue = submittedDate
    searchRange = ws.Range("A7:A37")
    Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues)
    
    If Not foundCell Is Nothing Then
        MsgBox "Value found!"
    Else
        MsgBox "Value not found..."
    End If
    
End Sub

Any thoughts/help would be appreciated and thank you in advance!

I can't seem to think of anything as a solution, as all the variables are declared ...

CodePudding user response：

The Find() method of the Range class needs a Range object to be called on, while

searchRange = ws.Range("A7:A37")

would result in a searchRange as an array storing the values of the cells "A7:A37", since the default property of the Range object is the Value one (it's as if you had coded searchRange.Value = ws.Range("A7:A37").

While in order to have a Range object you have to use the Set keyword

Set searchRange = ws.Range("A7:A37")

That said, you'd also better declare searchRange as of Range type

Dim searchRange As Range

which would have originated an error for Set searchRange = ws.Range("A7:A37") in the compiling phase, instead of the one you experienced in the runtime one

Finally, you can omit the declaration and use of submittedDate variable and go direct with the searchValue one only

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim searchValue As Variant
    Dim searchRange As Range
    Dim foundCell As Range
    
    Set ws = Sheet1 'setting it up so that I'm dealing with Sheet1
    ws.Range("A7:A37").NumberFormat = "dd-mmm" 'setting the number format of the values in A7:A37 as dd-mmm
    searchValue = Me.TextBox1.Value 'setting the input value as a variable
    Set searchRange = ws.Range("A7:A37")
    Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues)
    
    If Not foundCell Is Nothing Then
        MsgBox "Value found!"
    Else
        MsgBox "Value not found..."
    End If
    
End Sub

A more condensed version of which is the following one:

Private Sub CommandButton1_Click()

    Dim foundCell As Range
    With Sheet1.Range("A7:A37") ' reference the needed range
        .NumberFormat = "dd-mmm" 'setting the number format of the values in referenced range as dd-mmm
        Set foundCell = .Find(What:=Me.TextBox1.Value, LookIn:=xlValues) 'search for the TextBox1 value in the referenced range values
    End With
        If Not foundCell Is Nothing Then
            MsgBox "Value found!"
        Else
            MsgBox "Value not found..."
        End If
    
End Sub