Modification of Comments 1

[jqzhang]

I need to change a single word, like from 'ABC' to 'XYZ', in the comments of all my sheets (about 50 of them). Is there any way to make it quick, like 'Application.Substitute' or '.Replace' that can do it globally but they are for the cell values, not for comments.
Here is the Sub() I tried but sometimes it doesn't give me message, sometimes it does when I change the value that I want to modify. Generally, it doesn't work.
Thanks!
jqzhang

Public Function ColumnLetter(anyCell As Range) As String
ColumnLetter = Left(anyCell.Address( _
False, False), 1 - CInt(anyCell.Column > 26))
End Function

Public Function ColumnNumber(AlphaColumn As String) As Integer
ColumnNumber = Columns(AlphaColumn).Column
End Function

Sub ChangeComments()
For colIndex = ColumnNumber("C") To ColumnNumber("T")
If InStr(Range(ColumnLetter(Cells(1, colIndex)) & 10).Comment.Text, "ABC") Then
Range(ColumnLetter(Cells(1, colIndex)) & 10).Comment = _
Application.Substitute(Range(ColumnLetter(Cells(1, colIndex)) & 10).Comment.Text, _
"ABC", "XYZ")
End If
Next
End Sub

 

[PHV]

And what about something like this ?
For Each c in ActiveSheet.Range("C10:T10").Comments
c.Text = Replace(c.Text, "ABC", "XYZ")
Next c

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jqzhang]

PHV,
Thanks for the quick return! But I tried the similar way before and I'd got the same errors as I tried yours:

Run-time error "438"
Object doesn't support this property or method

Take care!
jqzhang

 

[PHV]

And this ?
For Each c in ActiveSheet.Comments
If c.Parent.Row = 10 Then
c.Text Replace(c.Text, "ABC", "XYZ")
End If
Next c

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jqzhang]

PHV,
Thanks a lot, PHV! It works really well. You're great in VBA, at least. I have tons of questions and hope I can bring them up to you.
Take care!
jqzhang

 

[jqzhang]

PHV,
I have a similar question and will be appreciative if you can give me an answer.
I tried to change CommandButton.Caption from 'ABC' to 'XYZ' and I have 10 buttons on a sheet and I have about 50 sheets. I used this one for a single sheet and it gave me message like 'Object required'.

Private Sub UserForm_Click()
Dim c As Object
For i = 1 To 8
Set c = "ActiveSheet.CommandButton" & i
If InStr(c.Caption, "ABC") Then
c.Caption Replace(c.Caption, "ABC", "XYZ")
End If
Next i
End Sub

Please advise!
Thanks!
jqzhang