Function behaving differently

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:

Code:

---

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:

Code:

---

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

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.

Code:

---

Function...
Set TargetCell = Worksheets("DP").Rows(1).Find(dDate)
   
If (Not TargetCell Is Nothing) Then
    Msgbox "TargetCell Found"
    End If

---

you may also try this one:

Code:

---

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

---

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.

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.

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

Quote:

---

Originally Posted by arnelgp

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.

Quote:

---

Originally Posted by 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.

---

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

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

Try another modification

Code:

---

'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

Your CallFunction function doesn't actually return anything.

Quote:

---

Originally Posted by 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

---

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 :)