Results 1 to 2 of 2
2014-11-19, 10:05 #1
- 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
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 Do 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
2014-11-19, 22:00 #2
- Join Date
- Aug 2010
- Pa, USA
- Thanked 246 Times in 226 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 errorhandler: MsgBox "False" End Sub
Last edited by Maudibe; 2014-11-19 at 22:23.
The Following User Says Thank You to Maudibe For This Useful Post:
CACI Matt (2014-11-20)