FindNext Problem RRS feed

  • Question

  • I have Workbook with several sheets, including a "Master" page.

    The Master page contains a cell for each student, the cell contains a Function call (to the Find/FindNext) routine what will find the Latest lesson date.

    The underlying Spreadsheets contain hundreds of entries.
    The entries are student lesson(s) information: Student Name, Lesson Date, Lesson Info...etc.

    When I test the function, by calling it from a standalone Sub in the module, it works fine.

    However, when I call it directly from the Cell, the routine apparently only finds the first entry, and then the FindNext is Nothing.

    Here is my code:

    Function fncTest(parmSheet As String, parmStudent As String) As Date
      Dim c As Range, firstAddress As String, strAddress As String, dteLesson As Date, dteLastLesson As Date
      Dim shtCurrent As Worksheet
      Dim boolEndLoop As Boolean
      Set shtCurrent = Worksheets(parmSheet)
      Debug.Print "In fncTest(" & parmSheet & ", " & parmStudent & ")"
      With shtCurrent.Range("a1:a999999")
        Set c = .Find(parmStudent, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
        Debug.Print "After .Find"
        Debug.Print "c.Address: " & c.Address
        strAddress = Replace(c.Address, "A", "B")
        Debug.Print "strAddress: " & strAddress
        dteLastLesson = CDate(shtCurrent.Range(strAddress))
        If (c Is Nothing) Then
          firstAddress = c.Address
            strAddress = Replace(c.Address, "A", "B")
            Debug.Print strAddress
            dteLesson = CDate(shtCurrent.Range(strAddress))
            If (dteLesson > dteLastLesson) Then
              dteLastLesson = dteLesson
            End If
            Debug.Print shtCurrent.Name & ": " & c.Address
            Set c = .FindNext(c)
            If (c Is Nothing) Then
              boolEndLoop = True
              If (c.Address = firstAddress) Then
                boolEndLoop = True
              End If
            End If
          Loop Until (boolEndLoop)
        End If
        fncTest = dteLastLesson
      End With
    End Function

    Again, if I call the function from a Sub (in the VBA editor): debug.print fncTest("Sheet1","<studentname>") it works correctly, but when I place =fncTest("Sheet1",A1) into a cell in another sheet, the FindNext creates a "c is Nothing" value…triggering an end to the loop.

    Any ideas as to why this would be happening, and how to rectify it? It’s driving me crazy.

    Thanks for your time in advance.


    Paul Goldstein

    Paul D. Goldstein Forceware Systems, Inc.

    Thursday, October 10, 2019 11:46 PM

All replies

  • Hi Paul D Goldstein,

    Sorry, I cannot understand the meaning of "call it directly from the Cell".
    Could you explain its meaning?


    Ashidacchi --

    Friday, October 11, 2019 7:39 AM
  • UDF's do not work with FindNext command when called from a worksheet. As you have found out, does work if called from another Sub.

    Following example code for a UDF works by looping the Find command without using the FindNext command. It finds all of the addresses for the find and stores them in an array and then joins the array and prints them out on the worksheet.

    Copy of the workbook at the following link. File is zipped so it does not automatically open with Excel on line that does not have all of Excel functionality so download and extract the file.!ArAXPS2RpafCrCvoBxKJVGhQk5oC?e=Dpy9kr

    UDF Code below for information.

    Please feel free to get back to me if still having problems with it.

    'Note strLookAt is either P for part or W for whole

    Function FindAddrs(varToFind As Variant, strLookAt As String, Optional rngToSearch As Range) As String
        Dim rngToFind As Range
        Dim strFirstAddr As String
        Dim arrList() As Variant
        Dim k As Long
        Dim lngLookAt As Long
        If UCase(strLookAt) = "W" Then
            lngLookAt = xlWhole
            lngLookAt = xlPart
        End If
        If rngToSearch Is Nothing Then  'If the User did not insert the Search Range parameter in the formula
            'Application.Caller is the cell containing the UDF formula
            'The Parent is the worksheet containing the Application.Caller cell
            'Cells is all of the cells on the worksheet.
            Set rngToSearch = Application.Caller.Parent.Cells
        End If
        k = 1   'Initialize array element counter to 1 for a one based array. (Can use zero for a zero based array)
        ReDim arrList(1 To k)      'Initialize with one element in the array so that Redim reserve will work
        With rngToSearch
            'Initialize rngToFind to last cell in the range to be searched
            'so that if first cell in range matches then it is found first
            Set rngToFind = .Cells(.Rows.Count, .Columns.Count)
                Set rngToFind = .Find(What:=varToFind, _
                                        After:=rngToFind, _
                                        LookIn:=xlFormulas, _
                                        LookAt:=lngLookAt, _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlNext, _
                                        MatchCase:=False, _
                If Not rngToFind Is Nothing Then
                    If strFirstAddr = "" Then   'Only assing the first found address to the variable
                        strFirstAddr = rngToFind.Address
                        'Test if find has looped around to the first found address
                        If strFirstAddr = rngToFind.Address Then Exit Do
                    End If
                    'Note ReDim Preserve does not increment number of elements on first loop
                    'Because k is still equal 1, it ReDims to one element as per value of k.
                    ReDim Preserve arrList(1 To k)
                    arrList(k) = rngToFind.Address  'Assign address to array
                    k = k + 1   'Increment ready for next ReDim Preserve.
                    Exit Do
                End If
        End With
        'Due to the array initialize with ReDim, ArrList will always
        'have the first element even if it contains no found value
        If arrList(1) <> "" Then     'Test if the first element of the array NOT a zero length string
            'Create a single string separated by the second parameter in the Join function
            FindAddrs = Join(arrList(), ", ")   'Join separates with a comma and a space.
            FindAddrs = varToFind & " not found"
        End If
    End Function

    Regards, OssieMac

    Friday, October 11, 2019 9:30 AM
  • Hi Ossie,

    Thank you for writing. I apologize for the delay…I was out Monday and Tuesday.

    Thank you for sending a workaround. I guess the real question is: Why doesn’t a UDF work with a FindNext?

    It sort of defeats the whole purpose for having the command as part of the language (VBA) when dealing with Excel. I even tried calling it as a separate routine, but it still has the same problem.

    I used the example that you gave and simply changed the .FindNext statement to a .Find with After:=<the last .Find range> command, and it worked fine.

    Thanks again,

    Paul Goldstein

    Paul D. Goldstein Forceware Systems, Inc.

    Wednesday, October 16, 2019 9:23 PM
  • I guess the real question is: Why doesn’t a UDF work with a FindNext?

    Hi Paul,

    Pleased for you that it is now working and thanks for posting the alternative method you used to edit your code.

    I agree with your comment. One would need to be one of the programmers who handle the Excel code to answer the question.

    I can't confirm it but I was told once that FindNext was an added functionality and in the original VBA code it was handled as per my work around for all Find Next code. When FindNext functionality was added, I am only speculating that it was an oversight for it not to work with a UDF called from a worksheet and it simply has never been fixed.

    You can submit suggestions at the following link but from personal experience they only introduce the fix in new versions of Excel and they are never fixed in the existing versions.

    Regards, OssieMac

    Wednesday, October 16, 2019 11:35 PM