FindNext in UDFs in Excel

[mlesniak001]

As has been noted in other posts, the FindNext does not work when used in a UDF (User Defined Function). This can be quite irritating / frustrating because the exact same code will work in a Sub.

However, for those wishing to have the FindNext feature available in a UDF, here is an alternative method (note: i did not think of this myself but found it on another forum; in fact it was this one:

http://www.ozgrid.com/forum/showthread.php?p=179978#poststop

).

Simply, this method is to use repeatedly the Find function with and employing the 'After' parameter.
Code:

Public Function FindRow(Crit As String, FindRng As Range, OldResults As Range) As Integer 
     
    Dim ans As Integer 
    Dim ans_add 
    Dim check 
    Dim c 
     
    With FindRng 
        ans_add = .Find(Crit, , lookat:=xlWhole).Address 
        ans = .Find(Crit, , lookat:=xlWhole).row 
    End With 
     
    For Each c In OldResults 
        If c.Value = ans Then 
            With FindRng 
                 'On Error GoTo ff
                ans = .Find(what:=Crit, after:=Cells(ans + 1, 1)).row 
                 'On Error GoTo 0
            End With 
        End If 
    Next 
     
ff: 
    On Error Goto 0 
    FindRow = ans 
     
End Function

again, credit goes to this guy:

http://www.ozgrid.com/forum/member.php?u=13163

 

[SkipVought]

Hi,

I would change the function data type from INTEGER to LONG since row numbers exceed 32,767, the max INTEGER value.

Skip,

_[red][/red]