Find Replace

  • Thread starter Thread starter bob
  • Start date Start date
B

bob

Is there a way to use this code and have it apply to Comments only?
I am trying to use the MatchCase and LookAt options.

tia


rngMyCell.Cells(xlCellTypeComments).Replace _
What:=strEdit_Find, Replacement:=strEdit_Replace, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True
 
Bob,

If you want to replace text in the comment text, you can't use
Find and Replace. You have to look in the Text property of the
Comment object and replace the text directly. For example,

Dim Rng As Range
For Each Rng In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)
Rng.Comment.Text Replace(Rng.Comment.Text(), "replace text",
"New Text")
Next Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
But you could use Edit|find to search through the comments. If you find it in
the comment, replace it there.

If you have lots of comments and few get changed, it might be quicker:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim FindWhat As String
Dim WithWhat As String

FindWhat = "ASDF"
WithWhat = "qwer"

Do
Set FoundCell = ActiveSheet.Cells.Find(What:=FindWhat, _
After:=ActiveCell, _
LookIn:=xlComments, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.Comment.Text _
application.substitute(FoundCell.Comment.Text, _
FindWhat, WithWhat)
End If
Loop

End Sub

And I used application.substitute. Chip used Replace. Replace will work in
xl2k and above.

(be aware that both Replace and application.substitute are case sensitive--so
it's probably a good idea to match case in the .Find.)
 
Back
Top