Find&Replace text in Comments
 

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

Find&Replace text in Comments
 

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

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

Find&Replace text in Comments
 

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

"Rafael Guerreiro Osorio"
wrote in message ...
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" wrote:

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

Find&Replace text in Comments
 

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

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

--

Dave Peterson

Find&Replace text in Comments
 

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

"John Svendsen" wrote:

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

"Rafael Guerreiro Osorio"
wrote in message ...
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" wrote:

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

Find&Replace text in Comments
 

The right tool for the right job...

"Dave Peterson" wrote:

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

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

--

Dave Peterson

Find&Replace text in Comments
 

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

Find&Replace text in Comments
 

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

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson

Find&Replace text in Comments
 

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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson

Find&Replace text in Comments
 

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

FoundCell.Comment.Shape.TextFrame.Characters.Repla ce FindWhat, WithWhat

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

John Svendsen wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Find&Replace text in Comments
 

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.

"Rafael Guerreiro Osorio" wrote:

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

"John Svendsen" wrote:

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

"Rafael Guerreiro Osorio"
wrote in message ...
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" wrote:

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