I am trying to create a custom function that will return a cell reference for
a search result. So far, I have this:

Function FindS(MySrch) As String

AC = Cells.Find(What:=MySrch, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Address

Ans = MsgBox("Search result (" & AC & ")", vbOKOnly, "RESULT")

End Function

I can capture the cell address in a message box but the active cell (where
the formula is) is empty. Ulitimately, I want to plug the results of 'FindS'
into another formula.

Everything I've tried has resulted in an error.
I feel like I'm close but I'm doing something fundamentally wrong.

Any ideas?

Thanks.

Mike

You have to assign the result to the function...
'--
Function FindS(ByRef MySrch As Variant) As String
Dim AC As String
AC = Cells.Find(What:=MySrch, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address
FindS = AC
End Function
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"MikeM_work"
wrote in message
I am trying to create a custom function that will return a cell reference for
a search result. So far, I have this:

Function FindS(MySrch) As String

AC = Cells.Find(What:=MySrch, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Address

Ans = MsgBox("Search result (" & AC & ")", vbOKOnly, "RESULT")

End Function

I can capture the cell address in a message box but the active cell (where
the formula is) is empty. Ulitimately, I want to plug the results of 'FindS'
into another formula.

Everything I've tried has resulted in an error.
I feel like I'm close but I'm doing something fundamentally wrong.

Any ideas?

Thanks.

Mike

Dear Jim:
Thanks so much for your quick reply.
As you might have guessed, I'm just getting started with VBA...
A simple but important step!
Mike

"Jim Cone" wrote:

> You have to assign the result to the function...
> '--
> Function FindS(ByRef MySrch As Variant) As String
> Dim AC As String
> AC = Cells.Find(What:=MySrch, After:=ActiveCell, LookIn:=xlFormulas, _
> LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Address
> FindS = AC
> End Function
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
>
>
>
> "MikeM_work"
> wrote in message
> I am trying to create a custom function that will return a cell reference for
> a search result. So far, I have this:
>
> Function FindS(MySrch) As String
>
> AC = Cells.Find(What:=MySrch, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
> :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
> False, SearchFormat:=False).Address
>
> Ans = MsgBox("Search result (" & AC & ")", vbOKOnly, "RESULT")
>
> End Function
>
> I can capture the cell address in a message box but the active cell (where
> the formula is) is empty. Ulitimately, I want to plug the results of 'FindS'
> into another formula.
>
> Everything I've tried has resulted in an error.
> I feel like I'm close but I'm doing something fundamentally wrong.
>
> Any ideas?
>
> Thanks.
>
> Mike
>