finding comments containing x

T

Terry P

Active Member
Joined
Nov 28, 2002
Messages
256
I would like to be able to input a variable, say x= inputbox, and show all comments on the page that contains x.
Any help appreciated.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
This is actually a UDF (user-defined function) that will return a count of the comments that contain your string, and make the comments visible.

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> CountCmt(<SPAN style="color:#00007F">ByRef</SPAN> Range <SPAN style="color:#00007F">As</SPAN> Range, <SPAN style="color:#00007F">ByRef</SPAN> FindWhat <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> <SPAN style="color:#00007F">ByRef</SPAN> CaseSensitive <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>

Application.Volatile

<SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> e <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> g <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> l <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> d <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> t <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

d = 0
g = Len(FindWhat)
f = FindWhat

<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range.Cells

s = 1
e = Len(c.Comment.Text)
t = c.Comment.Text

<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> s <= (e - g)

<SPAN style="color:#00007F">If</SPAN> s = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Do</SPAN>

<SPAN style="color:#00007F">If</SPAN> CaseSensitive <SPAN style="color:#00007F">Then</SPAN>
s = InStr(s, t, f, vbBinaryCompare)
<SPAN style="color:#00007F">Else</SPAN>
s = InStr(s, t, f, vbTextCompare)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">If</SPAN> s > 0 <SPAN style="color:#00007F">Then</SPAN> c.Comment.Visible = True: d = d + 1: s = s + g <SPAN style="color:#00007F">Else</SPAN> c.Comment.Visible = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#00007F">Loop</SPAN>

e = 0

<SPAN style="color:#00007F">Next</SPAN>

<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0

CountCmt = d

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>
 
Upvote 0
Excellent.
I've got to work on something else right now but will try it out later.
Thanks for such a quick reply!
 
Upvote 0
Ok back again!
I have never used a function before and would be grateful if you would tell me how to call it from vb eg x = Input box("xxxxx")...
Thanks
 
Upvote 0
First, paste code from above into a module in VBA, then you can use the function in a worksheet. Just enter it into a cell...

=CountCmt(A1:B12,"x",FALSE)
 
Upvote 0
Sorry TG
The comments are stored as:
WATTS
George John
If looking for John it will be displayed but if looking for George or Watts it won't unless it is the last term. Is there a way around it please?
 
Upvote 0
Here is the corrected function. Sorry!

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> CountCmt(<SPAN style="color:#00007F">ByRef</SPAN> Range <SPAN style="color:#00007F">As</SPAN> Range, <SPAN style="color:#00007F">ByRef</SPAN> FindWhat <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> <SPAN style="color:#00007F">ByRef</SPAN> CaseSensitive <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>

Application.Volatile

<SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> e <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> g <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> l <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> d <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> t <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> v <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>

d = 0
g = Len(FindWhat)
f = FindWhat

<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range.Cells

    s = 1
    e = Len(c.Comment.Text)
    t = c.Comment.Text
    v = <SPAN style="color:#00007F">False</SPAN>

    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> s <= (e - g)

        <SPAN style="color:#00007F">If</SPAN> s = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Do</SPAN>

        <SPAN style="color:#00007F">If</SPAN> CaseSensitive <SPAN style="color:#00007F">Then</SPAN>
            s = InStr(s, t, f, vbBinaryCompare)
        <SPAN style="color:#00007F">Else</SPAN>
            s = InStr(s, t, f, vbTextCompare)
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

        <SPAN style="color:#00007F">If</SPAN> s > 0 <SPAN style="color:#00007F">Then</SPAN> d = d + 1: s = s + g: v = <SPAN style="color:#00007F">True</SPAN>
    
    <SPAN style="color:#00007F">Loop</SPAN>

<SPAN style="color:#00007F">If</SPAN> v <SPAN style="color:#00007F">Then</SPAN> c.Comment.Visible = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Else</SPAN> c.Comment.Visible = <SPAN style="color:#00007F">False</SPAN>

e = 0

<SPAN style="color:#00007F">Next</SPAN>

<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0

CountCmt = d

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>
 
Upvote 0
I promise this is the last question!

The cell to call the function reads =CountCmt(1:1000,AC1,FALSE) so takes a while to work but that isn't a problem, it's still great.

When I press a button on the sheet to move to another sheet the Count function kicks in.

Is there a way to prevent this please?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
countifs with OR statement
Replies
2
Views
188
Mattlake
M
V
  • Question
Comment Macro
Replies
1
Views
298
Legacy 143009
L
spacecaptainsuperguy
  • Question
Clear contents of range of rows that don't contain certain text values or strings
Replies
4
Views
703
spacecaptainsuperguy
spacecaptainsuperguy
C
  • Question
Using the Find method to copy 4 cell values above Find = X to another sheet for every value of X found in a column
Replies
6
Views
343
chazrab
C
M
  • Question
Layered Filter issues on pivot
Replies
1
Views
345
Mattuckoo
M

Forum statistics

Threads
1,214,391
Messages
6,119,247
Members
448,879
Latest member
oksanana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top