Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Computing > NZ Computing > Excel Recorded Relative Macro with Search Function

Reply
Thread Tools

Excel Recorded Relative Macro with Search Function

 
 
lrolloatparadisedotnetdotnz
Guest
Posts: n/a
 
      08-08-2007
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
 
Reply With Quote
 
 
 
 
Alan
Guest
Posts: n/a
 
      08-08-2007
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:

http://www.velocityreviews.com/forums/(E-Mail Removed)

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:(E-Mail Removed)...
> 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



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
object-like macro used like function-like macro Patrick Kowalzick C++ 5 03-14-2006 03:30 PM
Excel relative command macro Query lrolloatparadisedotnetdotnz NZ Computing 5 05-23-2005 08:22 AM
search within a search within a search - looking for better way...my script times out Abby Lee ASP General 5 08-02-2004 04:01 PM



Advertisments