Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace All Macro

    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

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Replace All Macro

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

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Replace All Macro

    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.]

    <pre>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
    </pre>

    Gary

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace All Macro

    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.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace All Macro

    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.

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Willow Grove, Pennsylvania, USA
    Posts
    205
    Thanks
    4
    Thanked 49 Times in 40 Posts

    Re: Replace All Macro

    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

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Wildcard search trick

    > Replace: ^p^&

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

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

  8. #8
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Replace All Macro

    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.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace All Macro

    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. <A target="_blank" HREF=http://www.driveway.com/share?sid=b12f89b4.9f10b&name=Word+Tips
    Driveway Shared WordTips>http://www.driveway.com/share?sid=b12f89b4...&name=Word+Tips
    Driveway Shared WordTips</A>

    Hope this helps.

  10. #10
    Lounger
    Join Date
    Jan 2001
    Location
    Milton Keynes, Buckinghamshire, England
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace All Macro

    Phil,

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

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace All Macro

    Hi Robin:

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

  12. #12
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Replace All Macro

    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  13. #13
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Replace All Macro

    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...)

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace All Macro

    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.

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace All Macro

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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •