Search Comment Text

[John]

Hi,

I'm trying to write a procedure that will run through a column of data and
hide the rows that do not match a particular criteria. I'm fairly new at
this and so I'm sure this is a very obvious problem! I have a column of
data every cell of which has a comment. What I want to do is to enter a
search term to search within the comments text.........

The main difficulty I'm having is getting the active cell row and column
values (which I've called "r" and "c" respectively). At the moment the
ActiveCell.Row reads empty even when I can see the damn thing with a word in
it and a comment attached!

The x and y variables will be used as a message box at the end so I can see
how many have been filtered. I'll also be putting in a dialogue box to
capture the search term (although I might save that for another post unless
anyone is feeling extra helpful).

Anyway, thanks in advance

John



Sub SearchComments()

Dim SearchTerm As String
Dim rgCurrentCell As Range
Dim x As Variant
Dim y As Variant


'Check active cell at top of list
Answer = MsgBox(Prompt:="Is cell at top of comments filled list?",
Buttons:=vbYesNo + vbQuestion)
If Answer = vbNo Then Exit Sub

r = ActiveCell.Row
c = ActiveCell.Column

x = 0 'No. of visible rows
y = 0 'No. of processed rows

Set rgCurrentCell = Cells(r, c)

Do
If IsEmpty(Cells(r, c)) Then
MsgBox "Current cell is empty"
Exit Do
End If
If rgCurrentCell.Comment.Text = "music" Then x = x + 1 Else
Rows(r).EntireRow.Hidden = True

y = y + 1
r = r + 1
Loop


End Sub

 

[Dave Peterson]

I changed some of your variable names (x/y don't mean much when you're
debugging).

Option Explicit

Sub SearchComments()

Dim SearchTerm As String

Dim visRows As Long
Dim procRows As Long
Dim iRow As Long
Dim iCol As Long
Dim Answer As Long


'Check active cell at top of list
Answer = MsgBox(Prompt:="Is cell at top of comments filled list?", _
Buttons:=vbYesNo + vbQuestion)
If Answer = vbNo Then Exit Sub

iRow = ActiveCell.Row
iCol = ActiveCell.Column

visRows = 0 'No. of visible rows
procRows = 0 'No. of processed rows


Do
If IsEmpty(Cells(iRow, iCol)) Then
MsgBox "Current cell is empty"
Exit Do
End If
procRows = procRows + 1
If Cells(iRow, iCol).Comment Is Nothing Then
'do nothing???
Else
If LCase(Cells(iRow, iCol).Comment.Text) = "music" Then
Rows(iRow).Hidden = False
visRows = visRows + 1
Else
Rows(iRow).Hidden = True
End If
End If
iRow = iRow + 1
Loop

MsgBox visRows & vbLf & "of " & procRows
End Sub

 

[Wouter]

Hi,

Here is another option for you:

Sub SearchComments()
Dim lngRow As Long
Dim lngColumn As Long
Dim lngVisible As Long
Dim lngProcessed As Long
'
If MsgBox("Is cell at top of comments filled list?", _
vbYesNo + vbQuestion, _
"Start Process") = vbNo Then Exit Sub
'
lngColumn = ActiveCell.Column
lngRow = ActiveCell.Row
'
Do
If Cells(lngRow, lngColumn).Comment.Text = "music" Then
lngVisible = lngVisible + 1
Else
Cells(lngRow, lngColumn).EntireRow.Hidden = True
End If
lngProcessed = lngProcessed + 1
lngRow = lngRow + 1
Loop Until IsEmpty(Cells(lngRow, lngColumn))
MsgBox "Processed" & vbTab & CStr(lngProcessed) & vbNewLine & _
"Visible" & vbTab & CStr(lngVisible), vbOKOnly, "Ready"
End Sub

Greeting,

Wouter.

 

[John]

Dave and Wouter,

Thanks very much for this, it's worked perfectly. I've ended up taking
suggestions from both of you. I was interested to see the Is Nothing method
as I'd tried to use that but without success (now it works fine). Also
using the Loop Until is something I hadn't thought of (you can see my basic
knowledge level!).

Is there a particular benefit to using the LCase function? I've looked up
what it does, so does that mean that a search term of "music" would not pick
up "Music", or is it just best practise?

Thanks again for your help.

Best regards

John

 

[Dave Peterson]

Using the lCase depends on what you want to match. I figured that you wanted
MUSIC, Music, MuSiC, etc to "look" the same.

And unless you do something like this at the top of your module:
Option compare text
(changes comparisons to be not case sensitive)

You'll see a difference in your count.

====
And I use Do/Loop (with Until or While), just because I'm too lazy to remember
when these conditions get checked!