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