Search worksheet from add-in

  • Thread starter Thread starter BigJimmer
  • Start date Start date
B

BigJimmer

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).
 
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
 
Back
Top