Error : "Object variable or With block variable not set"

[Igor R.]

Hi!
This is the code that supposed to find the column with given value:

Sub main()
Dim s As Range
Set s = FindValueInCellRange(ThisWorkbook.Sheets(1).Range("A4:DO4"), "01/12/2022")
End Sub

Function FindValueInCellRange(MyRange As Range, MyValue As Variant) As Range
 With MyRange
        FindValueInCellRange = .Find(What:=MyValue, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,       SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
End With
End Function

This function works perfect when i use it in spreadsheet but prompt the error above when i use it in VBA.
I searched for solution in google but all i found did not help me.

Please, what I am doing wrong?

 

[vletm]

Igor R.
What do You have there before Find(?
.... MyRange.Find ... hmm?

 

[Igor R.]

Igor R.
What do You have there before Find(?
.... MyRange.Find ... hmm?

This

ThisWorkbook.Sheets(1).Range("A4:DO4")

 

[Marc L]

Hi,​

​

as your function has no sense so can never ever work ‼ Does not respect the Range.Find VBA help !​

First declared as Range but returns a String (Address) … ​

When nothing is found so another error rises 'cause you forgot to check if a Range is found (Not Is Nothing).​

​