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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >