excel - VBA function that will look in cell comment that have multiple lines for a specific line

Question

i have a code that will find if a cell comment have a certain word. However, i want it to look at separate lines. In range("A1:A10") some of the cells have comments and the rest does not, there is some comments that look like this

Fruit1: Apple
Fruit2: Orange
Fruit3: Banana

and the others look like this

Fruit1: Apple / Green apple
Fruit2: Orange 
Fruit3: Banana / Kiwi

with my current code, when i search for "Banana", this 2 comments will appear in the immediate window, however, i only want it to return the first comment. The following is my attempt on doing this

Sub testlike()
    Dim c As Range, rng As Range, cell As Range
    Dim fruit As String

    fruit = "Banana" 'Is going to be an input msgbox but for testing purposes, i left it at "Banana"
    Set rng = Range("A1:F10")
    For Each cell In rng
        Set c = cell.Find(fruit, LookIn:=xlComments)
        If Not c Is Nothing Then
            Debug.Print c.Comment.Text
        End If
    Next cell
End Sub

I am rather new to vba so i apologize if i seem ignorant . Thanks in advance!

标签： excelvba

Answer 1

Something like this?

Sub testlike()
    Dim rng As Range, cell As Range
    Dim arr As Variant
    Dim x As Long
    Dim fruit As String

    fruit = "Banana" 'Is going to be an input msgbox but for testing purposes, i left it at "Banana"
    Set rng = Range("A1:F10").SpecialCells(xlCellTypeComments)
    For Each cell In rng
        arr = Split(cell.Comment.Text, Chr(10))
        For x = LBound(arr) To UBound(arr)
            If arr(x) Like "*: " & fruit Then
                Debug.Print cell.Comment.Text
                Exit For
            End If
        Next x
    Next cell
End Sub

This is assuming that:

Fruit1: Apple
Fruit2: Orange
Fruit3: Banana

Is one comment.... or am I mistaken?