Find comments

[VegasNath]

Can you search an excel spreadsheet for comments?

[rory]

Yes. Using Activesheet.Usedrange.Specialcells(xlCellTypeComments) will get you a reference to all cells with comments (or an error if there are none).

[VegasNath]

Sorry Rory, I'm confused. How do I use this? In a sub, nothing happens.

[rory]

That code returns a reference to the range - you didn't say what you wanted to do with the comments, so I didn't add code to do anything! :)

[sdckapr]

Here is some code that will search for text within the comments:

Steve

Code: Select all

Option Explicit
Sub FindComment()
  Dim rStart As Range
  Dim cmt As Comment
  Dim sFind As String
  Dim Response

  sFind = InputBox("What do you want to search for?")
  If Trim(sFind) = "" Then
    MsgBox "Nothing entered"
    Exit Sub
  End If
  Set rStart = ActiveCell
  For Each cmt In ActiveSheet.Comments
    If InStr(LCase(cmt.Text), LCase(sFind)) <> 0 Then
      cmt.Parent.Select
      Response = MsgBox( _
        prompt:=cmt.Parent.Address & _
          vbCrLf & cmt.Text & vbCrLf & vbCrLf & _
          "Do you want to continue?", _
        Buttons:=vbYesNo, Title:="Continue?")
        If Response <> vbYes Then
          Set rStart = Nothing
          Exit Sub
        End If
    End If
  Next
  MsgBox "No more found"
  Set rStart = Nothing
End Sub

[Jan Karel Pieterse]

Excel search can also look in comments, but you have to tell it to look there in the options.

[VegasNath]

Hi, I apologise, I was not very clear. Ideally, I am after a list of cell references that contain comments, preferably in a msgbox.

[HansV]

Using Rory's suggestion:

MsgBox Activesheet.Usedrange.Specialcells(xlCellTypeComments).Address

[sdckapr]

Excel search can also look in comments, but you have to tell it to look there in the options.

The problem I found with that (and hence why I created the above) was that the find selects the cell but does not display what the comment contains (at least in XL2002). I wanted to see what cell it was in and what the comment was.

Steve

[VegasNath]

Diamond!

Cheers