VBA Find & replace text in comment

[JKHildi]

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

[nankw83]

Hi JKHildi & welcome to the forum,

Try below code on a copy of your workbook ...

Please Login or Register  to view this content.

PS: Please take a moment & edit your post, highlight the code then press the # button from the menu to insert code tags for you

[JKHildi]

That worked for the find and replace. Thank you nankw83!

[nankw83]

Glad to help & thanks for the Rep+

Please take a moment & mark the thread as [SOLVED] from the Thread Tools above your first post