Excel VBA code for find/replace

[Guest]

I am trying to write code in a VBA module that will find and replace all
instances of a given text within that module. The text replacement is to be
obtained through user input. Can anyone help? Thanks a lot!

 

[Tom Ogilvy]

Sub ReplaceItems()
ans = InputBox("Enter string to replace")
ans1 = InputBox("Replace it with what?")
if ans <> "" then
cells.Replace What:=ans, Replacement:=ans1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

End if
End Sub

This will replace substrings or whole words, case insensitive. If you want
changes only for a match at the whole value of the cell, change xlPart to
xlWhole

If you want case sensitive, change MatchCase:=True

If either ans or ans1 is specific (not prompted), then change the assignment
statement: For Example, If I want to just change the word "the" to whatever
the user specifies

ans = "the"
ans1 = InputBox("Replace ""the"" with what?")

 

[Guest]

This is right out of the Excel Help:

This example finds all cells in the range A1:A500 that contain the value 2
and changes their values to 5.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Not sure if it is what you are looking for, but maybe you can adapt.

 

[Jim May]

Tom:
If I had 30 worksheets, which were identical in structure,
And if I Grouped them and then on the 1st of the 30 sheets
then Selected only cell D311 that had in it --> =B123+b234+b432+b999
I suppose if I then ran ReplaceItems()
In the first inputbox if I entered +b432
And in the 2nd inputbox I entered +432+b500+b601
That afterwards ALL the cells D311 on All 30 sheets
Would contain:
=B123+b234+b432+b500+b601+b999 ?
Right?
Jim

 

[Guest]

No, the code as written works on all cells. Replace, does not work on
multiple selected sheets when executed in VBA.


Sub ReplaceItems()
Dim sh As Worksheet
Dim s As String, ans, ans1
ans = InputBox("Enter string to replace")
ans1 = InputBox("Replace it with what?")
s = Selection.Address(0, 0)
If ans <> "" Then
For Each sh In ActiveWindow.SelectedSheets
Set rng = sh.Range(s)
rng.Replace What:=ans, Replacement:=ans1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next
End If
End Sub

should be something like what you want.

Tested in xl2000

 

[Guest]

"Thanks for your response. What I really need to do is to find/replace the
code in the module, not on the worksheet. I have 12 worksheets, each
representing a separate month. I also have a number of worksheets, each
representing a separate account. My code copies the month end totals from
each "account worksheet" to the "month worksheet" . This involves moving
back and forth among worksheets to copy and paste. I want the user to
identify the new month and replace the previous month in the code designating
the active worksheet for the paste operation. Does this make any sense at
all? Is it possible?

 

[Tom Ogilvy]

It doesn't make any sense from a design/accepted practice standpoint.

Think you need to rethink your approach.

Best would be to eliminate any need to change code.

Next best would be to use declared constants at the top so only the
constants would need to be changed.

Code doesn't need to move back and forth to copy and paste.

sMonth = Format(date,"mmm")
set sh = worksheets(sMonth)
sh.Range("C20:F20").copy Destination:=Worksheets("Summary").Range("B5")

as an example.

 

[Guest]

Thanks, Tom. As you can tell, I'm a bit muddled on this. The find/replace
thing was code produced by recording a macro. I'm going to try rethinking
with your suggestiions. Thanks again.