+ Reply to Thread
Results 1 to 5 of 5

Find specific text and return coordinates

  1. #1
    BOONER
    Guest

    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.

  2. #2
    Rick Hansen
    Guest

    Re: 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" <BOONER@discussions.microsoft.com> wrote in message
    news:EF1009F6-409C-414D-B39B-CE3B661EDD6C@microsoft.com...
    > 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.




  3. #3
    BOONER
    Guest

    Re: 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" <BOONER@discussions.microsoft.com> wrote in message
    > news:EF1009F6-409C-414D-B39B-CE3B661EDD6C@microsoft.com...
    > > 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.

    >
    >
    >


  4. #4
    BOONER
    Guest

    Re: 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" <BOONER@discussions.microsoft.com> wrote in message
    > > news:EF1009F6-409C-414D-B39B-CE3B661EDD6C@microsoft.com...
    > > > 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.

    > >
    > >
    > >


  5. #5
    Rick Hansen
    Guest

    Re: 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" <BOONER@discussions.microsoft.com> wrote in message
    news:F170AEDF-6FEE-4829-92CE-6574628AE4B5@microsoft.com...
    > 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" <BOONER@discussions.microsoft.com> wrote in message
    > > news:EF1009F6-409C-414D-B39B-CE3B661EDD6C@microsoft.com...
    > > > 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.

    > >
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1