function fred Dim ws As Worksheet Dim v Set ws = Worksheets("outfile") set v = ws.Cells.Find(18709) If v Is Nothing Then Debug.Print "nothing" if not v is nothing then debug.print v.address exit function
The same code works (finds any value) when pasted into the debug window. But when called as an expression from a spread sheet, it fails, finding nothing.
A couple of things
- Functions need to be placed in a module or they won’t work
- Call the user function from Excel with [A1]:
- you can return a value to Excel if you change your code slightly:
fred = v.Address
Function fred() Dim ws As Worksheet Dim v Set ws = Worksheets("outfile") Set v = ws.Cells.Find(18709) If v Is Nothing Then fred = "nothing" If Not v Is Nothing Then fred = v.Address End Function