Find specific text and return coordinates
 

I need a macro that will find a cell containing given text (in one
workbook),and then return the cooridinates (column and row) of the cell below
it, to be used in a formula in another workbook.

Find specific text and return coordinates
 

Good Morning Booner-- Here try this code and see if what your looking for.
Post me back if you have questions.
enjoy, Rick (Fbks, Alaska)

Option Explicit

Function FindStr(FindMe As String) As String
Dim frng As Range

Set frng = Cells.Find(what:=FindMe, LookIn:=xlFormulas)
If Not frng Is Nothing Then
FindStr = frng.Offset(1, 0).Address(1, 1, xlA1)
Else
MsgBox ("Me, not found")
End If
End Function

Sub testIt()

Debug.Print FindStr("Me")
End Sub




"BOONER" wrote in message
...
I need a macro that will find a cell containing given text (in one
workbook),and then return the cooridinates (column and row) of the cell
below
it, to be used in a formula in another workbook.

Find specific text and return coordinates
 

What does this line do?


Set frng = Cells.Find(what:=FindMe, LookIn:=xlFormulas)


"Rick Hansen" wrote:

Good Morning Booner-- Here try this code and see if what your looking for.
Post me back if you have questions.
enjoy, Rick (Fbks, Alaska)

Option Explicit

Function FindStr(FindMe As String) As String
Dim frng As Range

Set frng = Cells.Find(what:=FindMe, LookIn:=xlFormulas)
If Not frng Is Nothing Then
FindStr = frng.Offset(1, 0).Address(1, 1, xlA1)
Else
MsgBox ("Me, not found")
End If
End Function

Sub testIt()

Debug.Print FindStr("Me")
End Sub




"BOONER" wrote in message
...
I need a macro that will find a cell containing given text (in one
workbook),and then return the cooridinates (column and row) of the cell
below
it, to be used in a formula in another workbook.

Find specific text and return coordinates
 

Thanks Rick,

I was able to incorporate this nicely. Figured out the line below too.
Thanks a lot

"BOONER" wrote:

What does this line do?


Set frng = Cells.Find(what:=FindMe, LookIn:=xlFormulas)


"Rick Hansen" wrote:

Good Morning Booner-- Here try this code and see if what your looking for.
Post me back if you have questions.
enjoy, Rick (Fbks, Alaska)

Option Explicit

Function FindStr(FindMe As String) As String
Dim frng As Range

Set frng = Cells.Find(what:=FindMe, LookIn:=xlFormulas)
If Not frng Is Nothing Then
FindStr = frng.Offset(1, 0).Address(1, 1, xlA1)
Else
MsgBox ("Me, not found")
End If
End Function

Sub testIt()

Debug.Print FindStr("Me")
End Sub




"BOONER" wrote in message
...
I need a macro that will find a cell containing given text (in one
workbook),and then return the cooridinates (column and row) of the cell
below
it, to be used in a formula in another workbook.

Find specific text and return coordinates
 

Booner, this line of code searches every cell on the Active worksheet to
find the string or text variable that was pass to this function, using the
Find method (see vba help). If the Find method finds the string variable in
one of the active worksheet cells, it set the range variable of 'frng' to
range location where the string was found. If the string was not found, then
range variable of 'frng' is set to 'Nothing'. The next line of code checks
to set if 'frng' was set to 'Nothing' (not found), if not then the string
was found. Since I I found the string location, I now have the address of
that location. Now using the 'Offset method I can now the find the address
the cell one row below it. As you requested in your macro. The FindStr()
function returns the absolute address, in the (A1) addressing format.
(See vba help on Address Method). I hope this explain what you were looking
for. Post back if more questions.

enjoy , Rick



"BOONER" wrote in message
...
What does this line do?


Set frng = Cells.Find(what:=FindMe, LookIn:=xlFormulas)


"Rick Hansen" wrote:

Good Morning Booner-- Here try this code and see if what your looking
for.
Post me back if you have questions.
enjoy, Rick (Fbks, Alaska)

Option Explicit

Function FindStr(FindMe As String) As String
Dim frng As Range

Set frng = Cells.Find(what:=FindMe, LookIn:=xlFormulas)
If Not frng Is Nothing Then
FindStr = frng.Offset(1, 0).Address(1, 1, xlA1)
Else
MsgBox ("Me, not found")
End If
End Function

Sub testIt()

Debug.Print FindStr("Me")
End Sub




"BOONER" wrote in message
...
I need a macro that will find a cell containing given text (in one
workbook),and then return the cooridinates (column and row) of the
cell
below
it, to be used in a formula in another workbook.