Search code examples
vbaexceluser-defined-functionsudf

Is it possible to use FindPrevious when searching with VBA code in a UDF?


I am trying to use VBA for a UDF to manipulate its results based on the "Find" function as it's much quicker than polling every cell looking for results.

I have cut it right back to it's basics to reproduce the error and I still get "Object variable or With block variable not set" error on this part SearchRange.FindPrevious.Address (You have to put a stop on the line and step it to get the error, UDF's just quit out without returning the error outside of debug mode)

Here's the kicker, I get the error when trying to use the UDF in a sheet like so: =testfind("3206-1",E:E) however when I use it in the immediate window in the VBE like so: ?testfind("3206-1",Range("E:E")) i get the correct result of $E$15295 without error.

Code is:

Function TestFind(FindString As String, SearchRange As Range)
Dim ActiveAddress As Range
Set ActiveAddress = SearchRange.Find(FindString)
TestFind = SearchRange.FindPrevious.Address
End Function

Modified with L42's changes and now it works:

Function TestFind(FindString As String, SearchRange As Range)
Dim ActiveAddress As Range
Set ActiveAddress = SearchRange.Find(FindString)
TestFind = SearchRange.Find(FindString, , , , , xlPrevious).Address
End Function

Solution

  • Edit1:

    This code

    TestFind = SearchRange.FindPrevious.Address
    

    and this as previously suggested:

    TestFind = SearchRange.FindPrevious(ActiveAddress).Address
    

    doesn't work when you use it as worksheet function as what Dan experienced.
    Below doesn't directly answer the question but just an alternative.

    Alternative: Change the SearchDirection argument in the Find method.

    Function TestFind2(FindString As String, SearchRange As Range)
        Dim ActiveAddress As Range
        Set ActiveAddress = SearchRange.Find(FindString, , , , , xlPrevious)
        TestFind2 = ActiveAddress.Address
    End Function
    

    Note: You need to add error handler to trap searches with no match found.

    How to test:

    Sub marine()
        Debug.Print TestFind(4, [A:A])
    End Sub