Find&Replace text in Comments

[John Svendsen]

Hi All,

I've been looking around for how does one Find and Replace text strings in
Cell Comments.
There is a lot of info on addind, deleting, text, but I did no find anything
on replacing/changing existing text.
I've tried
activecell.comment.text Replace(activecell.comment.text, "old", "new")
but no go
Does anyone have any ideas?
Thanks a lot
JS

 

[Guest]

Hi John,

Try a different approach using string functions. You might find the sample
code below useful:

Sub Sample()
MsgBox SubsTxt("with OLD text", "OLD", "NEW")
End Sub

Function SubsTxt(strComment, strOld, strNew As String)
Dim BeforeOld, AfterOld As String

'without error handling!!! strOld must be in strComment!!!
BeforeOld = Left(strComment, InStr(1, strComment, strOld) - 1)
AfterOld = Right(strComment, Len(strComment) - Len(BeforeOld) _
- Len(strOld))
SubsTxt = BeforeOld & strNew & AfterOld
End Function

Best,

Rafael

 

[John Svendsen]

Hi Rafael,

Thank you so much for replying - truly, I had not thought of doing a
repleace this way - ingenious.

However, my problem is how to access (find & replace) text in Comments
Object, which are in individual cells

Rgds, JS

 

[Dave Peterson]

Saved from a previous post:

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

application.substitute is case sensitive--so you'll have to match case.

 

[Guest]

If you mean that you have to find and replace more than one ocurrence of OLD,
its just a matter of making the function recursive, finding the first OLD,
then the second and so forth.

Comment doesn't have a Replace or Find Method. If you want to use START and
OVERWRITE of the Text method, default is overwrite, it will substitute
everything after START with TEXT; with overwrite set to false, it will insert
TEXT at START.

I used the Sub Sample below to find and change the text in a comment using
SubsTxt without any problem.

Sub Sample()
strNew = SubsTxt(Range("A1").Comment.Text, "OLD", "NEW")
Range("A1").Comment.Text strNew
End Sub

 

[Guest]

The right tool for the right job...

Saved from a previous post:

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

application.substitute is case sensitive--so you'll have to match case.

 

[John Svendsen]

Hi Dave/Rafael,

It is always good to listen from those who know

Question: When running this Find&Replace I notice that ALL text is now bold
(before, only the author of the note was in bold) after the replace - could
it be that since the first char/word in the Comment was bolded this cause
the whole text to be bolded?
Can this be fixed?

Again, thanks so much for your help and time
Rgds, JS

 

[Dave Peterson]

This'll take care of the bold problem--but it doesn't address anything else.
For instance, if you have different colors/font names/sizes, they'll be lost,
too. (That's why I like vanilla comments!)

But you could code around it -- keeping track of everything that's important and
then reapplying after the change. (A real pain!)

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim FindWhat As String
Dim WithWhat As String
Dim ColonPos As Long

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)

FoundCell.Comment.Shape.TextFrame.Characters.Font.Bold = False

ColonPos = InStr(1, FoundCell.Comment.Text, ":")

If ColonPos > 0 Then
FoundCell.Comment.Shape.TextFrame.Characters(Start:=1, _
Length:=ColonPos).Font.Bold = True
End If

End If
Loop

End Sub

This just removes all the bolding, then reapplies it to everything up to the
first colon in the comment.

 

[John Svendsen]

Hi Dave,
Again, thanks os much for replying. This code will do the trick nicely

I notice that to change text formatting, one uses:
Cell.Comment.Shape.TextFrame.Characters.<>
Cell.Comment.Shape.TextFrame.Characters.Font.<>

This seems similar the PowerPoint. In PPT I use:

If Shape.HasTextFrame Then
If Shape.TextFrame.HasText Then
Shape.TextFrame.TextRange.Replace FindWhat:=sFirst, _
Replacewhat:=sLast, MatchCase:=True, WholeWords:=True
End If
End If

In PPT, this code does not affect any formatting outside of the replaced
string.
Is is possible to use the .Comment.Shape.TexFrame. in Excel Cells in this
manner?

Rgds, JS

 

[Dave Peterson]

Excel's .textframe doesn't have a .textrange property and I couldn't use
..replace like:

FoundCell.Comment.Shape.TextFrame.Characters.Replace FindWhat, WithWhat

So I'd say no--well, until someone shows me what I missed.

Hi Dave,
Again, thanks os much for replying. This code will do the trick nicely

I notice that to change text formatting, one uses:
Cell.Comment.Shape.TextFrame.Characters.<>
Cell.Comment.Shape.TextFrame.Characters.Font.<>

This seems similar the PowerPoint. In PPT I use:

If Shape.HasTextFrame Then
If Shape.TextFrame.HasText Then
Shape.TextFrame.TextRange.Replace FindWhat:=sFirst, _
Replacewhat:=sLast, MatchCase:=True, WholeWords:=True
End If
End If

In PPT, this code does not affect any formatting outside of the replaced
string.
Is is possible to use the .Comment.Shape.TexFrame. in Excel Cells in this
manner?

Rgds, JS

 

[Guest]

How would this work within a macro where I want to replace the following
ActiveCell.FormulaR1C1 = "1/1/2004" all the first character with "2" and
so on. I am trying to replace 1 to 2 from January to February and do this
with all the months.