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

1 Answer 1

1

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
2
  • Yes, Yes, the problem is that the "FIND" doesn't work. FIND returns "nothing" as shown by "If v is Nothing". I'm not having trouble using functions in Excel, I having trouble using FIND. Aug 15, 2013 at 1:33
  • I can't reproduce your error. The macro is working just find when called from a spreadsheet. It finds any "18709" value and outputs the address to the immediate pane. If this code is part of a greater macro, be aware that Excel remembers all unspecified FIND parameters (such as after:=, lookat:= or lookin:=) from earlier FIND functions within the macro. Specify them with default values. Maybe you can upload your workbook?
    – nixda
    Aug 15, 2013 at 7:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .