Replace All Macro

Topic

New Reply

I’m trying to write a macro that puts a paragraph mark in front of every occurrence of the string “(x” where x is any letter but not a number. You can’t use the replace all box because it won’t work with wildcards. I bought a VBA book but still can’t figure out how to detect the last occurrence of the string in the open document and exit from whatever loop structure I’m using. Thanks in advance! – Randy

Reply | Quote

Replies

You could reverse your loop and search from bottom to top.

Reply | Quote

Hi Randy,

Here’s a macro I just wrote to replace one I’d had previously but lost!
It’s designed to do a Find/Replace continually as a loop until .Found is not True.
The purpose of this macro is to enable reducing all multiple spaces in a document to one space, without needing to re-run the replace.
Perhaps this could be adapted for your wildcard search:
{The ‘Do Until’ I had in there earlier was not necessary in this case; a simple Do does it.]

Sub FindReplaceAllWithLoop()
'Gary Frieder February 2001
'Will reduce all extra spaces (2, 3, 4 etc.) to one space
Do
    With Selection
        .HomeKey Unit:=wdStory
        With .Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .Text = "  " 'two spaces
            .Replacement.Text = " " 'one space
            .Forward = True
            .Wrap = wdFindStop 'important to use stop here
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
            .Execute
            If .Found Then
                .Execute Replace:=wdReplaceAll
            Else
                Exit Sub
            End If
        End With
    End With
Loop
End Sub

Gary

Reply | Quote

Hi Gary:

I don’t remember if I told you that Find/Replace would work in one fell swoop for this:
Using wildcards:
Find: “sp[2,]” w/o quotes, where sp= 1 space
Replace: “sp” w/o quotes

Then do Replace All.
This says to find 2 or more spaces & replace them with one space.

Hope this helps.

Reply | Quote

Hi Phil,

I think you did… (shame on me). Because I still haven’t taken the time to sit down and properly learn how to use wildcards, whenever I see a tip relating to them, it seems to go into non-durable memory! Fields and wildcards are my particular bugbears.

Now, to help me get over this sad state: I recall your posting, some time back, and possibly in a different forum, a masterly compendium you’d put together relating to Find/Replace, including use of wildcards (I think you quoted Robin Trew heavily in that part) – have I got that right? If so, would you be willing to post the link to that one again? – I promise I will sit down and read it forwards and back until I’m programming wildcards in my sleep!

Gary

PS: I did need to remember how to do that loop again, anyway – useful for other things too.

Reply | Quote

Hi Gary:

Here’s the link. I should again say that I quoted Robin Trew extensively, with her permission. In fact, the explanations are mostly hers & I put in lots of tables & examples. Download the 2nd file, if you have WinZip or something similar.

It’s at driveway.com. http://www.driveway.com/share?sid=b12f89b4…&name=Word+Tips
Driveway Shared WordTips

Hope this helps.

Reply | Quote

Here’s a poser for you Phil.

I have never worked out the gender of the very gifted Robin Trew. I assumed male, did you assume female or do you actually know?

Either way, Robin is sorely missed.

Reply | Quote

Hi Andrew,

On this delicate but amusing off-topic subject: I have to admit that in two years of posting here, as well as in the occasional personal correspondence I’ve had with Robin, I’ve always avoided the use of gender-specific pronouns, as I’ve never been sure!

Some time back, TomG did refer to Robin as “he”, and I might have done so in one post around that time.
Busy as Robin currently is, I know I’m not going to send a personal e-mail just to ‘pop the question’. Perhaps one of the real veterans around here (like Howard K.) can answer this one.

(Y’know, this same issue could arise around any number of names…)

Reply | Quote

Surely it would be determined by whether the Robin wore “Trews” or not?

Reply | Quote

Pundits beware

Reply | Quote

Trew

Reply | Quote

Hi Andrew:

I don’t know. In the U.S., Robin would generally be a woman’s name, but I don’t even know where Robin’s from. Yes, Robin is missed; maybe I’ll search for a website.

Reply | Quote

Hi Randy:

I may be missing the problem but it seems to me that the Find/Replace box will work.
Using wildcards
Find:([a-zA-Z]
Replace: ^p^&

Then do a replace all. Note that you must use a backward slash before the “(“.

Hope this helps.

Reply | Quote

Hi, Randy,

Forgive me if you already know this…

To use the Replace dialog with wildcards, you have to click the More button and check “Use wildcards”. Then you can use the Find and Replace strings Phil showed, and click Replace All.

You can also record a macro while you do this, so repeating it becomes a one-button job. You’ll see in the recorded code that the Selection.Find object has a .MatchWildcards property that is set to True, and the .Wrap property should be set to wdFindStop. The .Find.Execute method takes a Replace parameter that should be set to wdReplaceAll.

Jay

Reply | Quote

> Replace: ^p^&

Whoa. ^& means “the found stuff”? You have shown me the holy grail of wildcard searching!

(P.S. use ^13 instead of ^p)

Reply | Quote

Phil,

Perhaps I too am missing the problem, but wouldn’t this Find/Replace work just as well without using wildcards?
Find:(^$
Replace:^p^&

Reply | Quote

Hi Robin:

You’re so right!! I immediately jumped to the complex, overlooking the simple, straightforward way. Thanks for pointing that out.

Reply | Quote