Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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. |
#2
![]() |
|||
|
|||
![]()
..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 |
#3
![]() |
|||
|
|||
![]()
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 . |
#4
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
Trim function doesn't clean out ASCII Code 160 (Space) | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions |