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