Find comments

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Find comments

Post by VegasNath »

Can you search an excel spreadsheet for comments?
:wales: Nathan :uk:
There's no place like home.....

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Find comments

Post by rory »

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

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Find comments

Post by VegasNath »

Sorry Rory, I'm confused. How do I use this? In a sub, nothing happens. :confused:
:wales: Nathan :uk:
There's no place like home.....

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Find comments

Post by 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! :)
Regards,
Rory

User avatar
sdckapr
3StarLounger
Posts: 392
Joined: 25 Jan 2010, 12:21

Re: Find comments

Post by 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

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Find comments

Post by Jan Karel Pieterse »

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

Jan Karel Pieterse
Excel MVP jkp-ads.com

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Find comments

Post by 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.
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Find comments

Post by HansV »

Using Rory's suggestion:

MsgBox Activesheet.Usedrange.Specialcells(xlCellTypeComments).Address
Best wishes,
Hans

User avatar
sdckapr
3StarLounger
Posts: 392
Joined: 25 Jan 2010, 12:21

Re: Find comments

Post by sdckapr »

Jan Karel Pieterse wrote: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

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Find comments

Post by VegasNath »

Diamond!

Cheers :cheers:
:wales: Nathan :uk:
There's no place like home.....