Excel VBA code for find/replace
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
"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!
|