MS Excel VBA: Using .Find to search in cell comments

[BDCarrillo]

I'm not sure how to go about this... I need to find the row.id of text in a cell COMMENT that matches a string.

 Dim Rng2 As Range
    If Trim(strTCrsName) <> "" Then
        With Sheets("Transcript").Range("Q1:Q100")
            Set Rng2 = .Find(What:=strTCrsName, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

I'm not sure how to code it to "Look in the comments of the Q1:Q100 range of cells for a match"

If there's a way to kick out the comments from a different worksheet on the fly and "compare strTCrsName" to them, that'd probably work!

Thanks!

 

[SkipVought]

Hi,

Use the Comments collection...

dim cm as comment

for each cm in Sheets("Transcript").comments
  if cm.text like "*" & strTCrsName & "*" then
    'you found string in a comment
    MsgBox cm.Parent.Address
  end if
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

or this to coincide with the specified column...

    Dim cm As Comment
    With Sheets("Transcript")
        For Each cm In .Comments
          If cm.Text Like "*" & "skip" & "*" Then
            'you found string in a comment[b]
            If cm.Parent.Column = .Cells(1, "Q").Column Then _
                MsgBox cm.Parent.Row[/b]
          End If
        Next
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

...oops, I left my crumbs....

    If cm.Text Like "*" & strTCrsName & "*" Then

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BDCarrillo]

I modified the second, conditional one to suit my needs.

Thanks! I was going about it the wrong way.