Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can a custom Search function return a cell reference?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can a custom Search function return a cell reference?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can a custom Search function return a cell reference?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function to find value and return cell reference | Excel Discussion (Misc queries) | |||
how do I create a cell reference from ADDRESS function return? | Excel Worksheet Functions | |||
Search for data and return cell reference | Excel Worksheet Functions | |||
Which Function to Use? Search an Array, Return a Row Value | Excel Discussion (Misc queries) | |||
auto filter doesn't return cells containing the custom search wor. | Excel Worksheet Functions |