Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Nov 2014
    Thanked 0 Times in 0 Posts

    Search for Text within an inserted comment.


    Is the above possible.

    I have been playing around with an =getcomment function which can strip out all within an inserted comment and paste into a cell, but was wondering if a macro can search text thats in the inserted comment.
    I would ideally add it to the below macro which ....

    Searches for what i write in cell a1 on sheet "Returned Results" in the applicable tab, in this case "Substrates", and returns every row where the search word has been found in column A and also the header for that sheet.

    Problem is, that its not looking at the text in the inserted comments in column A which can be equally or sometimes more valid and important.
    I am guessing its a slight change to """ Set r = rng.Find(What:=strFind, LookAt:=xlPart, After:=.Cells(65536, "a")) """

    Any help would be much appreciated

    Many Thanks

    Sub Substrates()
        Dim strFind As String, rng As Range, x As Range, r As Range, ff As String
        With Sheets("Substrates")
            strFind = Sheets("Returned Results").Range("a1")
            Set rng = .Range("a:a")
            Set r = rng.Find(What:=strFind, LookAt:=xlPart, After:=.Cells(65536, "a"))
            If Not r Is Nothing Then
                Set x = r
                ff = r.Address
                    Set r = rng.FindNext(r)
                    If r.Address = ff Then Exit Do
                    Set x = Union(x, r)
                Loop Until r Is Nothing
                x.EntireRow.Copy Destination:=Sheets("Returned Results").Range("A25")
                Sheets("Substrates").Range("A1").EntireRow.Copy Destination:= _
                Sheets("Returned Results").Range("A24")
            End If
        End With
    End Sub
    Last edited by RetiredGeek; 2014-11-19 at 10:12. Reason: Added Code Tags

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Pa, USA
    Thanked 800 Times in 729 Posts

    I will point you in the right direction and let you take it from there

    Cell D1 has the comment. A msgbox will return True if the value in Cell A1 is contained in the comment and False if it doesn't. Stick with Search. It is more flexible than the Find method in that you can use wildcard characters and it is not case sensitive.


    Public Sub SearchText()
    On Error GoTo errorhandler
    If WorksheetFunction.Search(Range("A1"), Range("D1").Comment.Text, 1) Then
        MsgBox "True"
        Exit Sub
    End If
    MsgBox "False"
    End Sub
    Last edited by Maudibe; 2014-11-19 at 22:23.

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    CACI Matt (2014-11-20)

Posting Permissions

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