Writting Function using VBA

[nc]

Hi

I am trying to write a function to return an address but
instead I get #VALUE!.

Public Function fnd(a, b)

fnd = Range(a).Find(b).Address

End Function


Please help.

Thanks.

 

[Dave Peterson]

..Find won't work in UDF's called from the worksheet until xl2002.

Depending on the range (a), you could use application.match() through each
column.

If the range is small, you could just loop through the values in that range,
too.

 

[nc]

Hi Dave

Thanks for your response.

I am using Excel 2003.

-----Original Message-----
..Find won't work in UDF's called from the worksheet until xl2002.

Depending on the range (a), you could use

application.match() through each

 

[Dave Peterson]

This worked ok for me (also xl2003):

Option Explicit
Public Function fnd(a, b)
Dim FoundCell As Range

Set FoundCell = Range(a).Find(b)
If FoundCell Is Nothing Then
fnd = "Not Found"
Else
fnd = FoundCell.Address
End If

End Function

I used this in the cell:
=fnd("B1:B99",A1)

===
I think I'd also specify all the parameters in the .find statement. .Find
remembers all the ones you previously set--manually or via code.