Search and replace values and comments on a worksheet

[sofas]

Welcome. I am looking for a code that enables me to search and replace words or numbers, as well as comments. By showing the first InputBox to enter the search word and showing the second InputBox to enter the value that I want. I hope I can find someone to help me with this. Search range (A2:W200)

 

[DanteAmor]

In a copy of your sheet, try this:

Sub search_and_replace()
  Dim input_s As Variant, input_r As Variant
  Dim r As Range, cell As Range
  
  input_s = InputBox("search word")
  If input_s = "" Then Exit Sub
  input_r = InputBox("Replace word")
  If input_r = "" Then Exit Sub
  
  Set r = Range("A2:W200")
  r.Replace input_s, input_r, xlPart, , False
  
  For Each cell In r.SpecialCells(xlCellTypeComments)
    cell.Comment.Text Application.Substitute(cell.Comment.Text, input_s, input_r)
  Next
End Sub

Note: If you have formats in the comments, then adjustments would have to be made in the macro, but you must show in images how you have those formats or describe with examples, if this is not the case, then you will not have problems with the macro.

 

[sofas]

In a copy of your sheet, try this:

Sub search_and_replace()
  Dim input_s As Variant, input_r As Variant
  Dim r As Range, cell As Range
  
  input_s = InputBox("search word")
  If input_s = "" Then Exit Sub
  input_r = InputBox("Replace word")
  If input_r = "" Then Exit Sub
  
  Set r = Range("A2:W200")
  r.Replace input_s, input_r, xlPart, , False
  
  For Each cell In r.SpecialCells(xlCellTypeComments)
    cell.Comment.Text Application.Substitute(cell.Comment.Text, input_s, input_r)
  Next
End Sub

Note: If you have formats in the comments, then adjustments would have to be made in the macro, but you must show in images how you have those formats or describe with examples, if this is not the case, then you will not have problems with the macro.

Great. Thank you. Can I add a notification msgbox when the value does not exist, in other words, search and replace?
I tried this but it didn't work when the value was found within the comment and not in the cells
Wherever the search word is found it should be replaced

 

[DanteAmor]

Wherever the search word is found it should be replaced

The macro replaces the word in cells and also in comments.

Check the following and tell me what type of comment you have in the cells:

The difference between threaded comments and notes - Microsoft Support

Comments and notes are new in Excel for Office 365. This article explains the differences.

support.microsoft.com