Page 1 of 2 12 LastLast
Results 1 to 10 of 13

finding comments containing x

This is a discussion on finding comments containing x within the Excel Questions forums, part of the Question Forums category; I would like to be able to input a variable, say x= inputbox, and show all comments on the page ...

  1. #1
    Board Regular
    Join Date
    Nov 2002
    Posts
    256

    Default finding comments containing x

    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.

  2. #2
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202

    Default Re: finding comments containing x

    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.

    Function CountCmt(ByRef Range As Range, ByRef FindWhat As String, Optional ByRef CaseSensitive As Boolean) As Double

    Application.Volatile

    Dim c As Range
    Dim s As Long
    Dim e As Long
    Dim g As Long
    Dim l As Long
    Dim d As Double
    Dim t As String
    Dim f As String

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

    On Error Resume Next

    For Each c In Range.Cells

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

    Do While s <= (e - g)

    If s = 0 Then Exit Do

    If CaseSensitive Then
    s = InStr(s, t, f, vbBinaryCompare)
    Else
    s = InStr(s, t, f, vbTextCompare)
    End If

    If s > 0 Then c.Comment.Visible = True: d = d + 1: s = s + g Else c.Comment.Visible = False

    Loop

    e = 0

    Next

    On Error GoTo 0

    CountCmt = d

    End Function

  3. #3
    Board Regular
    Join Date
    Nov 2002
    Posts
    256

    Default Re: finding comments containing x

    Excellent.
    I've got to work on something else right now but will try it out later.
    Thanks for such a quick reply!

  4. #4
    Board Regular
    Join Date
    Nov 2002
    Posts
    256

    Default Re: finding comments containing x

    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

  5. #5
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202

    Default Re: finding comments containing x

    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)

  6. #6
    Board Regular
    Join Date
    Nov 2002
    Posts
    256

    Default Re: finding comments containing x

    Great! thanks a million

  7. #7
    Board Regular
    Join Date
    Nov 2002
    Posts
    256

    Default Re: finding comments containing x

    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?

  8. #8
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202

    Default Re: finding comments containing x

    Here is the corrected function. Sorry!

    Function CountCmt(ByRef Range As Range, ByRef FindWhat As String, Optional ByRef CaseSensitive As Boolean) As Double

    Application.Volatile

    Dim c As Range
    Dim s As Long
    Dim e As Long
    Dim g As Long
    Dim l As Long
    Dim d As Double
    Dim t As String
    Dim f As String
    Dim v As Boolean

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

    On Error Resume Next

    For Each c In Range.Cells

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

    ****Do While s <= (e - g)

    ********If s = 0 Then Exit Do

    ********If CaseSensitive Then
    ************s = InStr(s, t, f, vbBinaryCompare)
    ********Else
    ************s = InStr(s, t, f, vbTextCompare)
    ********End If

    ********If s > 0 Then d = d + 1: s = s + g: v = True
    ****
    ****Loop

    If v Then c.Comment.Visible = True Else c.Comment.Visible = False

    e = 0

    Next

    On Error GoTo 0

    CountCmt = d

    End Function

  9. #9
    Board Regular
    Join Date
    Nov 2002
    Posts
    256

    Default Re: finding comments containing x

    Brilliant old chap. As we say over here. I can sleep peacefully now.
    Many thanks.

  10. #10
    Board Regular
    Join Date
    Nov 2002
    Posts
    256

    Default Re: finding comments containing x

    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?

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com