Writting Function using VBA
 

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.

..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 wrote:

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

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
column.

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

nc wrote:

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
.

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.

nc wrote:

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