excel - VBA 函数将在具有特定行的多行的单元格注释中查找

Question

我有一个代码可以查找单元格注释是否包含某个单词。但是，我希望它查看单独的行。在 range("A1:A10") 中，一些单元格有注释，其余的没有，有些注释看起来像这样

Fruit1: Apple
Fruit2: Orange
Fruit3: Banana

其他的看起来像这样

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

使用我当前的代码，当我搜索“香蕉”时，这 2 条评论将出现在即时窗口中，但是，我只希望它返回第一条评论。以下是我这样做的尝试

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

我对 vba 比较陌生，所以如果我看起来很无知，我深表歉意。提前致谢!

Answer 1

像这样的东西？

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

这是假设:

Fruit1: Apple
Fruit2: Orange
Fruit3: Banana

是一条评论....还是我弄错了？