Hi there,
I have found plenty of information on how to change the comments for the entire workbook/worksheet, but I need to modify that down to just a range.
I am also wondering if this can be modified to find "HDPE" in the comment, but lookup the replace value from another cell. I am no VBA wizard, so thank you in advance for your help! I would like to replace two values in the comments. They would be my variable. The replacement values would come from cells D124 and E124. This process would then need to repeat until the row is empty. Hopefully, that is clear. Let me know what info you need from me in order to help.
Here is the code currently....
Sub ReplaceComments()
Dim cmt As Comment
Dim wks As Worksheet
Dim sFind As String
Dim sReplace As String
Dim sCmt As String
Dim rng As Range
sFind = "HDPE"
sReplace = "SDR-35"
Set rng = Range("k108:R121")
For Each cmt In rng.Comments
sCmt = cmt.Text
If InStr(sCmt, sFind) <> 0 Then
sCmt = Application.WorksheetFunction. _
Substitute(sCmt, sFind, sReplace)
cmt.Text Text:=sCmt
End If
Next
End Sub
Bookmarks