Manual Input During Macro Execution

[Sherlock]

I am trying to set up a macro that contains a section that will execut
a Find & Replace function. I would prefer that it would copy th
contents of a specific cell and paste it into the Find field and the
do the same with the Replace field. Unfortunately, I can't find a wa
to do this.

My second option would be to have the macro pause, let me manuall
input the Find & Replace fields, and then continue with the rest of th
macro. Any bright ideas from the masses

 

[Harald Staff]

Hi

In cases like this, record a macro while doing it manually and you're almost
there. Recorded and edited:

Sub Test()
Cells.Replace What:=Range("A1").Value, _
Replacement:=Range("A2").Value, _
LookAt:=xlPart, _
MatchCase:=False
End Sub

(Note that it will also replace the What value the A1 cell

HTH. Best wishes Harald

 

[Trevor Shuttleworth]

"Any bright ideas from the masses?"

Is that the same as the peasants ?

 

[Sherlock]

Harald - You are a genius! Worked great - Thanks!

Trevor - I told you never to post until you finished plowing th
fields. Off with your head!

In order to become a little more self sufficient, do you have to ge
this stuff from Visual Basic books? I sure don't see much out there i
any of the Excel manuals

 

[Jako]

Sherlock.

I have 2 Excel VBA books.

Here are rhe details.

Excel 2000 VBA - Programmers Reference
Published by Wrox
Written by John Green
$24.99
ISBN 1-861002-54-8

Writing Excel Macros 2nd Edition
Published by O'Reilly
Written by Stephen Roman
$34.95
ISBN 0-596-00359-5

Also have the Excel 200 Bible which has some chapters on VBA but no
worth getting if you want advanced programming.

Hope this helps.

Also, although not recommended due to legal reasons,
searches for .pdf files on peer 2 peer programs will bring up Exce
orientated Ebooks.

They range from 2Mb upto 90Mb.

Please don't shoot the messenger !!!!

The people who write these books deserve the monies they get i
royalties. Without these people (and the many MVP's on this forum wh
give advice for no cost) people like myself would never
have the opportunity to create their own applications.

We owe them all a great debt

 

[Sherlock]

Jako,

Thanks for the references. I'll check them out