macro help needed

qmann

Board Regular
Joined
May 9, 2003
Messages
94
You all have been extremely helpful and a need a little bit of a hand. I'm kind of getting away from excel here but he outcome i'm sure would be the same. This is actually a word problem.
I need to set up a button macro that will search for a string and if it finds that string replace it with another string, then autosave the document.

So if I need to change every value of H001 with H00 then autosave what would be the code for that macro.

Like i said i know this is getting away from excel but you guys have been extremely helpful in the paste and you all seem to know your macros very well.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
I hate Word, but if you're forced to use it, why not try the recorder?

Here's what I got:
Code:
Sub FindReplace()

    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "H001" ' I suppose you could add an Input Box here if you wanted
        .Replacement.Text = "H00"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    ActiveWindow.ActivePane.SmallScroll Down:=-9

End Sub
I imagine that there's a way to get rid of the Selects, but I don't mess with Word, let alone use VBA with it. :unsure:

Hope that helps,

Smitty
 

TommyGun

MrExcel MVP
Joined
Dec 9, 2002
Messages
4,202
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ReplaceCode()
    <SPAN style="color:#00007F">Dim</SPAN> findValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> replaceValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    findValue = "code"
    replaceValue = "stuff"
    
    <SPAN style="color:#00007F">With</SPAN> ActiveDocument.Range.Find
        .ClearFormatting
        .Text = findValue
        <SPAN style="color:#00007F">With</SPAN> .Replacement
            .ClearFormatting
            .Text = replaceValue
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        .Execute MatchCase:=True, MatchWholeWord:=True, Format:=True, Replace:=wdReplaceAll
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 

qmann

Board Regular
Joined
May 9, 2003
Messages
94

ADVERTISEMENT

Thanks for your reply!! I want it all to correspond to a button click so i have inserted a button and i have the save aspect no problem.. but am still having a problem with the find replace.

I have:

Private Sub CommandButton1_Click()
f = "F:\Save Template\"
ActiveDocument.SaveAs f & Format(Now(), "DDMMYYYYHHSS") & ".doc"
End Sub

This works fine for the saving but i am still having a tough time getting the find replace going
 

TommyGun

MrExcel MVP
Joined
Dec 9, 2002
Messages
4,202
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    <SPAN style="color:#00007F">Dim</SPAN> findValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> replaceValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    findValue = "H001"
    replaceValue = "H00"
    
    <SPAN style="color:#00007F">With</SPAN> ActiveDocument.Range.Find
        .ClearFormatting
        .Text = findValue
        <SPAN style="color:#00007F">With</SPAN> .Replacement
            .ClearFormatting
            .Text = replaceValue
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        .Execute MatchCase:=True, MatchWholeWord:=True, Format:=True, Replace:=wdReplaceAll
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

    ActiveDocument.SaveAs "F:\Save Template\" & Format(Now(), "DDMMYYYYHHSS") & ".doc"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,222
Messages
5,594,904
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top