Find/Replace Macro to Word

Hi,

I have the following Macro;

Please Login or Register  to view this content.

.Text = "NAME1"
.Replacement.Text = Range("C2")

This works for all NAME1 entries in the Word document but I want to have multiple searches for example;

.Text = "NAME1" & "NAME2"
.Replacement.Text = Range("C2") & Range("C3")

So that it will replace NAME1 and 2 with what is in C2 and C3. I want to do about a hundred of these.

Also I would like some assistance on how to modify this macro so the user cannot interact with it and once the changes are complete it Prints and Closes the word document.

Many thanks.

Please Login or Register  to view this content.

Ok thanks very much but I don't quite get your modification;

Will this replace my entire code?

"Name1" & "Name2", , , , , , , , , c01, 2

Are all the commas there telling me I can replace like;

"Name1" & "Name2" & "Name3" & "Other" and son on. And I guess the 2 is the amount of find replaces it should do.

I also get Object does not support this method error on line;

.Selection.Find.Execute "Name1" & "Name2", , , , , , , , , c01, 2

Thanks very much.

EDIT: Added +rep for you.

The routines can be easily adapted to handle arrays or ranges for the Find and Replace strings. If you post a simple example xlsm and docx, I can show you more easily or just explain where the data for Find and Replace strings comes from.

Find and replace needs are not always simple. Some structures are more challenging so example files to help you is usually best.

Please use the Word VBEditor's helpfiles to improve your VBA knowledge (e.g. find.execute ). Do not 'suppose' things, but check them.

A slight modification; this is all the code you'll need.

Please Login or Register  to view this content.

I posted a working example....

Yes yours works it seems but with .Close -1 I don't see the changes it just leave a blank word process open and not the document.

From here I would need to apply more changes than just NAME1 and 2, unsure how I add to that code with all those , , , , , , ,

Also would need to keep the document open in the background and disable events/user input while it performs and print then close.

We are on the right tracks though, thanks very much.

EDIT:

snb I removed the close function and it remained open, the Macro does not replace anything though but runs without error.

Fantastic, this works. Thanks so much.

Just a last question before I add to this;

It is dealing with Ranges, Range("B2:B12"), Range("C2:C12"), True, False

I can't think how to amend it so I can add more ranges or a single cell find/replace.

After thats done I think I can complete it. To think ive been doing this for around 13 hours.

Alternatively if I wanted to manually set all the findable words in the macro instead of using range, how would I do that;

.Text = "Applicant One Name"
.Replace = "Range("C2")
.Text = "Applicant Two Name"
.Replace = "Range("C3")
.Text = "New Payment"
.Replace = "Range("B71")

I know this will make the macro massive but using Ranges will cause problems when it comes to things like B15:B24.

Thanks.

You can use Union() or just use discontinuous ranges like this below. One cell is just Range("B2") for example. You may want to check for the case where the Replace cell value is "". IsEmpty() can be used for that as you may not want that deleted but then maybe you do. The find string being empty might be something to check as well.

Please Login or Register  to view this content.

For single cells to Find/Replace using this same Sub routine:

Please Login or Register  to view this content.

I like the bookmark method better or a mail merge better than Find/Replace. Howsoever, when deadlines approach, any method will suffice.

The first example you showed then seem more like what I need but how does that long set of ranges know what text to look for?

Range("B2:B12, B15:B24, B27:B35, B38:B63, B66:B70")

How will each of these know how to look for different text?

Can we not get rid of this and use the manual way;

.Text = "Applicant One Name" + "Applicant Two Name" + Address Line One + Address Line Two
.Replacement.Text = Range("C2") + Range("C3") + RangeC8 + Range C9 and so on.

So I can define what text to find in the macro not be looking at a range in the worksheet first. If we can apply this to the current macro then I am all set.

It does not matter whether you pass one cell or many cells, the .Value property is used to get the value. In the sub that does the work, it gets the first cell in the passed Range of cells by:

Please Login or Register  to view this content.

Since your replace range had cells that were merged, one has to do this sort of thing. I just did it for the Find range as well just to be on the safe side. Had I been sure that it would never be a merged cell, it could have been:

Please Login or Register  to view this content.

If you are going to concatenate strings, use "&" rather than "+". If you want to set just one find and one replace string, my linked example will do the job. You would need to get the first merged cell if you are getting a merged cell value as I did of course. Use the Immediate window in VBE and type your concatenation string with a ? as the first character and press enter. It is a quick debugging method.

If you want to Find/Replace multiple times and not use my SearchMReplaceInDoc() routine, then you need another method as I explained early in this thread. Arrays, Dictionary methods, Collections, Delimited strings are just a few of the methods one can use. Of course that would require a modification to SearchMReplaceInDoc().

I have given you 3 solutions. I don't know how many solutions you want. This last Test sub showed how to send one Find/Replace at a time. It also showed how to setup an array so that it could do many in a "loop". In the array a(), the first dimension 1 or the x in a(x,y). It is the Find string. Think of it as a column. The second dimension of a() is the Replace string y in the array, a(x, y). Think of it as a row though some think opposite but arrays don't care. This is the same as if we set a range of 2 columns and 2 rows.

The first solution that I gave is the more robust. It took care of the merged cell issue. You will have to do that yourself if you use most any other method.

The forum changed the work DDIICCKK (less duplicated letters) to "****".
The search replace was:

Search Replace
Tom 1

By array a() For Loop:
DDIICCKK 2
Harry 3

Ken 4.

So you see: Tom, DDIICCKK, Harry, Ken; are the search strings. The replace strings were: 1, 2, 3, 4. The first dimension x in a(x, y) will be either 1 for the search string or 2 for the replace string.

Notice that by the parameters sent to the sub, the first call did one search/replace group. The array method looped and did 2 calls to the sub. In all 3 calls, the parameters were set to not close the MSWord application nor the DOC.

For the last call, it did close MSWord, if it was not opened, and then closed the DOC. Of course when it does, it will ask you to save it. You can record a macro to do the save and print as needed just after "If closeDoc".

Almost there...

Please Login or Register  to view this content.

PS. If I were you I would save the result. In your code you don't.

NB. the wordconstant 2 is equivalent to wdreplaceall.
the argument 'forward' is redundant if using wdreplaceall.

No progressbar needed when using my code, nor screenupdating =false nor....