FIND method doesn't work in UDF. What's wrong with this code?

Windows XP - Excel 2000
Hello,
I'm trying to make a function that returns the row number of the cell
in range SAR where string CRIT appears for the first time.
The purpose is to use the result as an argument in an INDEX function to
retrieve the value of the intersection of a certain row and column.

The INDEX(MATCH,MATCH) formula is useless in this case since the
horizontal criteria are not on the same row.

What is wrong with the following code ? I always get the #VALUE!
result. (I know about line breaking underscores missing in this
message. They are OK in the module.)

Function GetRowNumber(SAR As Range, CRIT As String) As Long
GetRowNumber = SAR.Find(What:=CRIT, After:=SAR.Range("A1"),
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Row
End Function

THE FOLLOWING SUB-VERSION WORKS FINE THOUGH.
Sub RowNum()
Dim SAR As Range, CRIT As String
Set SAR = Application.InputBox("Select Search Area", Type:=8)
CRIT = Application.InputBox("Enter Search Value", Type:=2)
MsgBox SAR.Find(What:=CRIT, After:=SAR.Range("A1"), LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Row
End Sub

Can anyone help me out here?
Thank you very much
Herman

Hi Herman,

Find indeed doesn't work in a UDF. This is probably because there is a
replace option and changing worksheets from a UDF is prohibited.
Subs however do have permission to change worksheets, so Find does work
there.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

<hermac@pandora.be> wrote in message
news:1120904523.896684.211150@g47g2000cwa.googlegroups.com...
> Windows XP - Excel 2000
> Hello,
> I'm trying to make a function that returns the row number of the cell
> in range SAR where string CRIT appears for the first time.
> The purpose is to use the result as an argument in an INDEX function to
> retrieve the value of the intersection of a certain row and column.
>
> The INDEX(MATCH,MATCH) formula is useless in this case since the
> horizontal criteria are not on the same row.
>
> What is wrong with the following code ? I always get the #VALUE!
> result. (I know about line breaking underscores missing in this
> message. They are OK in the module.)
>
> Function GetRowNumber(SAR As Range, CRIT As String) As Long
> GetRowNumber = SAR.Find(What:=CRIT, After:=SAR.Range("A1"),
> LookIn:=xlFormulas, _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False).Row
> End Function
>
> THE FOLLOWING SUB-VERSION WORKS FINE THOUGH.
> Sub RowNum()
> Dim SAR As Range, CRIT As String
> Set SAR = Application.InputBox("Select Search Area", Type:=8)
> CRIT = Application.InputBox("Enter Search Value", Type:=2)
> MsgBox SAR.Find(What:=CRIT, After:=SAR.Range("A1"), LookIn:=xlFormulas,
> _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False).Row
> End Sub
>
> Can anyone help me out here?
> Thank you very much
> Herman
>

Hi Niek,

Find DOES work in UDF's. An example which works fine for me:
----- snip here --------
Public Function holidays(ByVal s_ccy As String) As Variant
'Returns array of holidays for a given currency

Dim rng_ccy As Range

With Sheets("Holidays")
Set rng_ccy = .Range("A2:IV2").Find(s_ccy, LookIn:=xlValues,
LookAt:=xlWhole) 'Search for currency in row 2
If rng_ccy Is Nothing Then
holidays = CVErr(xlErrValue) 'Not found: return value error
Else
holidays = .Cells(5, rng_ccy.Column).Resize(.Cells(3,
rng_ccy.Column), 1) 'Row 3 consists of number of holidays
End If
End With

End Function
------- snip again -----------

Herman, I suggest to debug your code in detail step by step...

Regards,
Bernd
--
Use non-volatile INDEX(P11:IV65536,1+w,1+y):INDEX(P11:IV65536,w+y,x+z)
instead of volatile OFFSET(P11,w,x,y,z).

Thanks Niek.
You taught me something important.
Do you have any suggestions on how to get that row number, either with
a UDF or a WS function ?
Herman

Hallo Bernd,
Thanks for your help.
But how can I debug a code with only one line ?
And by the way, what do you mean by
"Use non-volatile
INDEX(P11:IV65536,1+w,1+y):IND=ADEX(P11:IV65536,w+y,x+z)
instead of volatile OFFSET(P11,w,x,y,z). "

Kind Regards,
Herman

Hi Herman,

You can set a breakpoint at that line (left mouse-click in the editor to the
left of that line, one or more bordeaux-red dots will appear) and you can add
watches for interesting values (for example for SAR and for CRIT: right
mouse-click at CRIT and again at SAR and add a watch for them). Then start
the calculation. The macro will be interrupted at the breakpoint and you can
see the watched values at this state.

By the way: Your macro has no problem in my example (A1:A8 filled with
a,b,c,...,h; cell C4 filled with h, C5 = getrownumber(A1:A8,C4) returns 8
which is ok.

My statement below "--" just shows my general opinion not to use the OFFSET
worksheet function. Does not apply to your problem.

Regards,
Bernd

Find started working in xl2002. Before that, it didn't work in UDFs.

Are you using xl2002+?

Bernd Plumhoff wrote:
>
> Hi Niek,
>
> Find DOES work in UDF's. An example which works fine for me:
> ----- snip here --------
> Public Function holidays(ByVal s_ccy As String) As Variant
> 'Returns array of holidays for a given currency
>
> Dim rng_ccy As Range
>
> With Sheets("Holidays")
> Set rng_ccy = .Range("A2:IV2").Find(s_ccy, LookIn:=xlValues,
> LookAt:=xlWhole) 'Search for currency in row 2
> If rng_ccy Is Nothing Then
> holidays = CVErr(xlErrValue) 'Not found: return value error
> Else
> holidays = .Cells(5, rng_ccy.Column).Resize(.Cells(3,
> rng_ccy.Column), 1) 'Row 3 consists of number of holidays
> End If
> End With
>
> End Function
> ------- snip again -----------
>
> Herman, I suggest to debug your code in detail step by step...
>
> Regards,
> Bernd
> --
> Use non-volatile INDEX(P11:IV65536,1+w,1+y):INDEX(P11:IV65536,w+y,x+z)
> instead of volatile OFFSET(P11,w,x,y,z).

--

Dave Peterson

Hi Dave,

Yes, my version is Excel 2002.

Regards,
Bernd

Hi Niek,

Sorry - wrong version, as Dave pointed out.

Regards,
Bernd

Thanks, Dave, I didn't know that!

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42CFBB87.1C6C1894@verizonXSPAM.net...
> Find started working in xl2002. Before that, it didn't work in UDFs.
>
> Are you using xl2002+?
>
> Bernd Plumhoff wrote:
>>
>> Hi Niek,
>>
>> Find DOES work in UDF's. An example which works fine for me:
>> ----- snip here --------
>> Public Function holidays(ByVal s_ccy As String) As Variant
>> 'Returns array of holidays for a given currency
>>
>> Dim rng_ccy As Range
>>
>> With Sheets("Holidays")
>> Set rng_ccy = .Range("A2:IV2").Find(s_ccy, LookIn:=xlValues,
>> LookAt:=xlWhole) 'Search for currency in row 2
>> If rng_ccy Is Nothing Then
>> holidays = CVErr(xlErrValue) 'Not found: return value error
>> Else
>> holidays = .Cells(5, rng_ccy.Column).Resize(.Cells(3,
>> rng_ccy.Column), 1) 'Row 3 consists of number of holidays
>> End If
>> End With
>>
>> End Function
>> ------- snip again -----------
>>
>> Herman, I suggest to debug your code in detail step by step...
>>
>> Regards,
>> Bernd
>> --
>> Use non-volatile INDEX(P11:IV65536,1+w,1+y):INDEX(P11:IV65536,w+y,x+z)
>> instead of volatile OFFSET(P11,w,x,y,z).
>
> --
>
> Dave Peterson

hermac@pandora.be wrote:
> Thanks Niek.
> You taught me something important.
> Do you have any suggestions on how to get that row number, either with
> a UDF or a WS function ?
> Herman
>
If the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook

=INDEX(ArrayMatch("CRIT",SAR),1,1)+ROW(SAR)-1

Alan Beban