Find and Replace
 

I have a macro that I use to replace the contents of cells on a
worksheet which have a comment attached with an "F" in the cell (in
place of an asterisk "*"). It works fine most of the time but at
times when I run it fails miserably changing the contents of all non-
blank cells in the workbook to "F". It seems that it does this
because the "within" setting in my Find and Replace wizard has been
set to "Workbook" rather than "Sheet" prior to running the macro.

The relevant lines of code in my macro (which were recorded) are

Selection.SpecialCells(xlCellTypeComments).Select
Selection.Replace What:="*", Replacement:="F", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

I surmised that the LookAt:=xlPart was the bit of code that should set
the search to sheet only, but it doesn't seem to be doing what I think
it should be doing.

I guess this is probably a quite simple thing, but if someone could
help me resolve it it would be appreciated.

TIA
Ron

Find and Replace
 

try:

Selection.Replace What:="~*",

The asterisk is a wildcard representing any set of characters. The tilde in
front of it means to treat the * as an asterisk--not a wildcard.

Same thing with ? (Use ~?) and ~ (use ~~).

wrote:

I have a macro that I use to replace the contents of cells on a
worksheet which have a comment attached with an "F" in the cell (in
place of an asterisk "*"). It works fine most of the time but at
times when I run it fails miserably changing the contents of all non-
blank cells in the workbook to "F". It seems that it does this
because the "within" setting in my Find and Replace wizard has been
set to "Workbook" rather than "Sheet" prior to running the macro.

The relevant lines of code in my macro (which were recorded) are

Selection.SpecialCells(xlCellTypeComments).Select
Selection.Replace What:="*", Replacement:="F", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

I surmised that the LookAt:=xlPart was the bit of code that should set
the search to sheet only, but it doesn't seem to be doing what I think
it should be doing.

I guess this is probably a quite simple thing, but if someone could
help me resolve it it would be appreciated.

TIA
Ron

--

Dave Peterson