Searching within comments added to cells
 

Is it possible to write VBA code that will search inside the comments
attached to a cell in excel for each sheet in the workbook.

The number and names of the sheets will constantly be changing.

Ideally what I'd like to do is something like:

1. Go to first sheet in workbook
2. for each sheet in workbook search the comments for desired info
3. On finding result return message stating the sheet name and cell that
the comment is attached to and the choice to end searching here and go to
that cell or continue searching
4. On not finding result return message with simple 'OK' button and end
the macro.

Thanks for any help/pointers in the right direction.

Searching within comments added to cells
 

Hi,

A starter which looks for text "comment" in comments. If found, it
makes the comment visible - I don't know how you get the cell address.

Sub FindComments()
Dim c As Object, ws As Worksheet, resp
Dim cFound As Boolean
For Each ws In Worksheets
ws.Activate
cFound = False
For Each c In ActiveSheet.Comments
If InStr(1, c.Text, "Comment") < 0 Then
resp = MsgBox(ws.Name & vbCrLf & vbCrLf & "Do you want to continue
search?", vbYesNo, "Search for comments")
cFound = True
If resp = vbNo Then
c.Visible = True
Exit Sub
End If
End If
Next c
If Not cFound Then
MsgBox "No comment found in " & ws.Name
End If
Next ws

End Sub

HTH

"red6000" wrote:

Is it possible to write VBA code that will search inside the comments
attached to a cell in excel for each sheet in the workbook.

The number and names of the sheets will constantly be changing.

Ideally what I'd like to do is something like:

1. Go to first sheet in workbook
2. for each sheet in workbook search the comments for desired info
3. On finding result return message stating the sheet name and cell that
the comment is attached to and the choice to end searching here and go to
that cell or continue searching
4. On not finding result return message with simple 'OK' button and end
the macro.

Thanks for any help/pointers in the right direction.

Searching within comments added to cells
 

Hi Red6000,

Try:

Sub SearchComments(Optional myStr As Variant)

Dim blFound As Boolean
Dim blNoMatch As Boolean
Dim sStr As String
Dim StrText As String
Dim rng As Range
Dim WS As Worksheet
Dim rCell As Range
Dim res As Long
Dim i As Long, j As Long
Dim msg As String
Dim mybuttons As Long
Dim myTitle As String
Dim SearchTxt 'As String


If IsMissing(myStr) Then
SearchTxt = InputBox _
("Enter text to be found in comments")

If StrPtr(SearchTxt) = 0 _
Or Len(SearchTxt) < 1 Then
MsgBox "No searchstring entered!", _
vbInformation, "Search Aborted"
Exit Sub
End If
Else
SearchTxt = myStr
End If

i = ActiveWorkbook.Worksheets.Count

For Each WS In ActiveWorkbook.Worksheets
On Error Resume Next
Set rng = WS.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
j = j + 1
sStr = rCell.Comment.Text
blFound = InStr(1, sStr, SearchTxt, vbTextCompare)
If blFound Then
msg = SearchTxt & _
" found in comment at " _
& rCell.Address(0, 0, , 1)

If WS.Index < i And j < rng.Cells.Count Then
mybuttons = vbYesNo
myTitle = "CONTINUE SEARCH?"
Else
mybuttons = vbInformation
myTitle = "Comment Texr Srearch"
End If
res = MsgBox(msg, mybuttons, myTitle)

blNoMatch = True
If res = 7 Then
Application.Goto rCell
Exit Sub
End If

End If
Next rCell
End If
Set rng = Nothing
Next
If blNoMatch = False Then
MsgBox SearchTxt & " not found in any comments", _
vbInformation, "No Match!"
End If
End Sub


---
Regards,
Norman



"red6000" wrote in message
...
Is it possible to write VBA code that will search inside the comments
attached to a cell in excel for each sheet in the workbook.

The number and names of the sheets will constantly be changing.

Ideally what I'd like to do is something like:

1. Go to first sheet in workbook
2. for each sheet in workbook search the comments for desired info
3. On finding result return message stating the sheet name and cell that
the comment is attached to and the choice to end searching here and go to
that cell or continue searching
4. On not finding result return message with simple 'OK' button and end
the macro.

Thanks for any help/pointers in the right direction.