Consulting

Results 1 to 13 of 13

Thread: Function behaving differently

  1. #1

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

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    327
    Location
    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.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Function...
    Set TargetCell = Worksheets("DP").Rows(1).Find(dDate)
        
    If (Not TargetCell Is Nothing) Then
        Msgbox "TargetCell Found"
        End If
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    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

  5. #5
    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.
    Attached Files Attached Files

  6. #6
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    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.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

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

  8. #8
    Quote Originally Posted by arnelgp View Post
    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.

  9. #9
    Quote Originally Posted by georgiboy View Post
    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.

  10. #10
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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
    Last edited by Artik; 01-16-2023 at 05:17 PM.

  11. #11
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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

  12. #12
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    Your CallFunction function doesn't actually return anything.
    Be as you wish to seem

  13. #13
    Quote Originally Posted by Artik View Post
    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 )

    Thanks all for your help though

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •