I am trying to search for specific text in a worksheet. I need to do this
from a udf that is in an add-in, and the search does not return a value (rng
= Nothing). If I copy the logic to a module in the workbook itself, it
works.

dim rng as Range
dim Wb as Workbook

Wb = ActiveWorkbook

Set rng = Wb.Worksheets("Sheet1").Columns(1).Find("mystring" )

Thanks!

Hi,
I suppose you are using the function in a worksheet eg: =MyFucntion(...)

VBA for Excel is quite strict when developing Worksheet UDFs. Not only you
cannot use methods that change things eg: change a format, sheet name..., but
also, some other methods/properties cannot be used Eg: Range.Find ,
Range.SpecialCells, Range.CurrentRegion, Range.CurrentArray....

If you use one of the above, the function may become unstable and the
execution of the function may exit before reaching the End Function (test
with debug.Print here and there).

--
Regards,
Sébastien
<http://www.ondemandanalysis.com
<http://www.ready-reports.com


"BigJimmer" wrote:

I am trying to search for specific text in a worksheet. I need to do this
from a udf that is in an add-in, and the search does not return a value (rng
= Nothing). If I copy the logic to a module in the workbook itself, it
works.

dim rng as Range
dim Wb as Workbook

Wb = ActiveWorkbook

Set rng = Wb.Worksheets("Sheet1").Columns(1).Find("mystring" )

Thanks!

You can use .Find() in UDFs in xl2002+.

Before that, you have to use a different technique. Application.match() is one
that may work for you.

Dim Res as variant
dim myRng as range
set myrng = Wb.Worksheets("Sheet1").Columns(1)
res = application.match("mystring",myrng,0)

if iserror(res) then
'not found
else
msgbox myrng(res)
end if



BigJimmer wrote:

I am trying to search for specific text in a worksheet. I need to do this
from a udf that is in an add-in, and the search does not return a value (rng
= Nothing). If I copy the logic to a module in the workbook itself, it
works.

dim rng as Range
dim Wb as Workbook

Wb = ActiveWorkbook

Set rng = Wb.Worksheets("Sheet1").Columns(1).Find("mystring" )

Thanks!

--

Dave Peterson