Search/Replace in Comments in Excel

[Lonely]

Hi All,
I have been trying to write a macro which will allow me to replace all occurences of "Business" to "XXX" in the comments of all cells in an Excel Worksheet.

please can someone help me in the process.
thanks,
Lonely

[Chien9999]

In fact, I'm not very good at VBA, I only make some small macro for some excel file. But let's try this :

Sub Comment()
Range(Cells(1, 1), Cells(1000, 100)).Select
Selection.ClearComments
For i = 1 To 1000
j = 1
Do
c = InStr(1, Trim(Cells(i, j)), "Business", 0) ' Choose 1 if you don't care for exact
If c > 0 Then
Cells(i, j).Select
Selection.AddComment
Selection.Comment.Text Text:="XXXX"
End If
j = j + 1
Loop Until j = 100
Next
End Sub

You can see my excel sample attched.

Cheers

[Lonely]

Hi Chien,
Thank you very much for taking time off to help me out. However, with the help of some of my friends, i was able to come up with something like:


Sub comments_replace()

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

FindWhat = "User"
WithWhat = "XXX"

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



Thought you might be interested in knowing :)

Thanks,
Lonely