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
Printable View
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
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
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:
VB Code:
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