Range类的Find()方法需要在Range对象上调用，而

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

将得到一个searchRange，作为存储单元格“A7：A37”的值的数组，因为Range对象的默认属性是Value（就好像您编写了searchRange.Value = ws.Range("A7:A37")一样。
而要获得Range对象，则必须使用Set关键字

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

也就是说，最好将searchRange声明为Range类型

Dim searchRange As Range

这将在编译阶段引发Set searchRange = ws.Range("A7:A37")错误，而不是您在运行时遇到的错误
最后，您可以省略submittedDate变量的声明和使用，直接使用searchValue变量

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

其更精简的版本如下：

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