Excel Recorded Relative Macro with Search Function

Discussion in 'NZ Computing' started by lrolloatparadisedotnetdotnz, Aug 8, 2007.

  1. A macro is intended to capture a sting value in a source cell,
    move to another worksheet, and search for the string, with a number of
    actions to follow, with the cursor finally coming to rest in the cell
    below the original start cell, ready to be repeated.

    In the recorded macro code below, the Find string persists and
    prevents the macro from using a subsequent find value.

    Is there a simple way of clearing the find value after it was
    located the target cell, so that the string value of the next source
    cell will be recorded to carry out the succeeding search?

    ActiveCell.Select
    Selection.Copy
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    Cells.Find(What:="13AE8V", After:=ActiveCell, LookIn:=xlFormulas,
    LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
    MatchCase:= _
    False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 12).Range("A1").Select
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
    ActiveCell.Offset(0, 11).Range("A1").Select
    < remainder of code follows>



    Lindsay Rollo
    Wellington, New Zealand
     
    lrolloatparadisedotnetdotnz, Aug 8, 2007
    #1
    1. Advertising

  2. lrolloatparadisedotnetdotnz

    Alan Guest

    Hi Lindsay,

    This line is the problem:

    Cells.Find(What:="13AE8V", After:=ActiveCell, LookIn:=xlFormulas,
    LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,MatchCase:= _
    False, SearchFormat:=False).Activate

    You need to make the "13AE8V" be a reference to a cell value something
    like this:

    Cells.Find(What:=Selection.value, After:=ActiveCell,
    LookIn:=xlFormulas,
    LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,MatchCase:= _
    False, SearchFormat:=False).Activate

    That assumes that the macro runs with the cell you want to be the
    source of the find string selected.

    HTH,

    --

    Alan.

    The views expressed are my own, and not those of my employer or anyone
    else associated with me.

    My current valid email address is:



    This is valid as is. It is not munged, or altered at all.

    It will be valid for AT LEAST one month from the date of this post.

    If you are trying to contact me after that time,
    it MAY still be valid, but may also have been
    deactivated due to spam. If so, and you want
    to contact me by email, try searching for a
    more recent post by me to find my current
    email address.

    The following is a (probably!) totally unique
    and meaningless string of characters that you
    can use to find posts by me in a search engine:

    ewygchvboocno43vb674b6nq46tvb




    <lrolloatparadisedotnetdotnz> wrote in message
    news:...
    > A macro is intended to capture a sting value in a source cell,
    > move to another worksheet, and search for the string, with a number
    > of
    > actions to follow, with the cursor finally coming to rest in the
    > cell
    > below the original start cell, ready to be repeated.
    >
    > In the recorded macro code below, the Find string persists and
    > prevents the macro from using a subsequent find value.
    >
    > Is there a simple way of clearing the find value after it was
    > located the target cell, so that the string value of the next source
    > cell will be recorded to carry out the succeeding search?
    >
    > ActiveCell.Select
    > Selection.Copy
    > ActiveSheet.Next.Select
    > ActiveSheet.Next.Select
    > Cells.Find(What:="13AE8V", After:=ActiveCell, LookIn:=xlFormulas,
    > LookAt _
    > :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
    > MatchCase:= _
    > False, SearchFormat:=False).Activate
    > ActiveCell.Offset(0, 12).Range("A1").Select
    > ActiveSheet.Previous.Select
    > ActiveSheet.Previous.Select
    > ActiveCell.Offset(0, 11).Range("A1").Select
    > < remainder of code follows>
    >
    >
    >
    > Lindsay Rollo
    > Wellington, New Zealand
     
    Alan, Aug 8, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. robk98

    digital camera with a good macro function

    robk98, May 22, 2004, in forum: Digital Photography
    Replies:
    4
    Views:
    3,945
    Mark Johnson
    May 24, 2004
  2. Lamont Franklin

    Info on macro function

    Lamont Franklin, May 2, 2005, in forum: Digital Photography
    Replies:
    3
    Views:
    265
    Lamont Franklin
    May 2, 2005
  3. Macro Function w/ Vivitar Vivicam 4100

    , Feb 11, 2006, in forum: Digital Photography
    Replies:
    5
    Views:
    518
  4. lrolloatparadisedotnetdotnz

    Excel Macros - Recording Relative Values ?

    lrolloatparadisedotnetdotnz, May 18, 2005, in forum: NZ Computing
    Replies:
    3
    Views:
    552
    lrolloatparadisedotnetdotnz
    May 18, 2005
  5. lrolloatparadisedotnetdotnz

    Excel relative command macro Query

    lrolloatparadisedotnetdotnz, May 22, 2005, in forum: NZ Computing
    Replies:
    5
    Views:
    854
    lrolloatparadisedotnetdotnz
    May 23, 2005
Loading...

Share This Page