Excel VBA Find fails when called from spreadsheet

Posted on

QUESTION :

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.

Any suggestions?

ANSWER :

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]: =fred()
  • you can return a value to Excel if you change your code slightly: fred = v.Address
  • change exit function to end function

enter image description here


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

Leave a Reply

Your email address will not be published.