«

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

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