Function behaving differently

[cosmarchy]

I have a strange problem with a with two functions behaving differently even though they are essentially the same.

First of all, I have this sub:

Private Sub FindDate1()

Const dDate As String = "1/12/2023"

Dim TargetCell As Range

Set TargetCell = Worksheets("DP").Rows("1").Find(What:=CDate(dDate), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
If (Not TargetCell Is Nothing) Then
Debug.Print TargetCell.Column
End If

End Sub
which works as it should. That is to say that it returns the column of the cell which contains 1/12/23

Now, when I 'enhance' this a little to this:

Private Function FindDate2(dDate As String) As Range

Dim TargetCell As Range

Set TargetCell = Worksheets("DP").Rows("1").Find(What:=CDate(dDate), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
If (Not TargetCell Is Nothing) Then
Set FindDate2 = TargetCell
End If

End Function
it stops working entirely. The Find function returns nothing and therefore the function does not return anything.

Why this is strange is that the Find function is exactly the same in both the sub and function. So, how can it find the field in one but not the other???

Any ideas?

Thanks

[June7]

I am trying both procedures and the Find does not return any result in either.

I can get Worksheets("DP").Range("A:C").Find( to work in both.

[SamT]

Function...
Set TargetCell = Worksheets("DP").Rows(1).Find(dDate)

If (Not TargetCell Is Nothing) Then
Msgbox "TargetCell Found"
End If

[arnelgp]

you may also try this one:


Private Function FindDate2(dDate As String) As Range
Dim srchFor As Variant
Dim TargetCell As Range
srchFor = DateValue(Format$(dDate, "mm/dd/yyyy"))

'Set TargetCell = Worksheets("DP").Cells(1, 1).Find(What:=dDate, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
Set TargetCell = Worksheets("DP").Cells.Find(What:=CDate(dDate), After:=Range("A1"), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If (Not TargetCell Is Nothing) Then
Set FindDate2 = TargetCell
End If

End Function

[cosmarchy]

Hi,

I've tried all the suggested ideas and a few more besides (some of which I have left in the code!!). I've tried jiggling around with the function too and just cannot get it to work so have uploaded the workbook in case this proves useful.

[georgiboy]

If you make row 2 actual dates and format them to only show the day number, then edit your search row to row 2 you should have more joy with it.

[arnelgp]

you need to format your date to EN-US format (mm/dd/yyyy).

[cosmarchy]

you need to format your date to EN-US format (mm/dd/yyyy).

Did this work on your computer? I cannot get this to work as TargetCell always returns nothing.

[cosmarchy]

If you make row 2 actual dates and format them to only show the day number, then edit your search row to row 2 you should have more joy with it.

I've tried this suggestion but I have the same issue where TargetCell is still returning nothing.

[Artik]

The problem of the Find method used in the function not working when the function is used as a sheet function is well-known. You have to use another method to search.

..::Edit
I have to backtrack from the last sentences. The problem is with older versions of Excel (2000)
::..

Artik

[Artik]

Try another modification
'arnelgp & Artik
Public Function FindDate3(iRow As Integer, WS As Worksheet) As Range
Dim TargetCell As Range
Const dDate As Date = #12/1/2023# '= 01 Dec 2023 'date must be in mm/dd/yyyy (US-date format)

'We are looking for the date as formatted text as in the local settings
Set TargetCell = WS.Rows(1).Find(What:=Format(dDate), After:=WS.Range("A1"), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

Set FindDate3 = TargetCell

End Function
Artik

[Aflatoon]

Your CallFunction function doesn't actually return anything.

[cosmarchy]

The problem of the Find method used in the function not working when the function is used as a sheet function is well-known. You have to use another method to search.

..::Edit
I have to backtrack from the last sentences. The problem is with older versions of Excel (2000)
::..

Artik

Yep, I think you're right. I've come up with another way now basically iterating through the range looking for a date value (probably how the find function works anyway :think:)

Thanks all for your help though :)