"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" wrote:

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

--
Regards,
Tom Ogilvy


"Jim May" wrote:

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 +b432+b500+b601
That afterwards ALL the cells D311 on All 30 sheets
Would contain:
=B123+b234+b432+b500+b601+b999 ?
Right?
Jim

"Tom Ogilvy" wrote in message
:

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?")

--
Regards,
Tom Ogilvy


"Needles" wrote in message
...

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!