excel - 将多个 MsgBox 合二为一

Question

当我们打开此工作簿时，此代码将查看一个范围并找到包含文本“RCA Pending”的单元格，并将弹出一个 MsgBox，让用户知道该列中的哪一行包含特定文本。问题是，如果有多行包含此文本，也会有多个 MsgBox 弹出窗口。

Private Sub Auto_Open()

Dim i As Variant
Dim FindRange As Range
Set FindRange = Range("AB2:AB2000")

    For Each i In FindRange
        If i = "RCA Pending" Then

        MsgBox "Found 'RCA Pending' in cell" & " " & i.Address, vbExclamation, "Attention"

        End If
    Next i
End Sub

此代码中需要更改哪些内容，以便只有一个弹出窗口列出找到“RCA Pending”的所有行？

Answer 1

试试这个代码:

Private Sub Auto_Open()    

Dim i As Variant
Dim FindRange As Range
Dim Msg As String
Set FindRange = Range("AB2:AB2000")

    For Each i In FindRange
       If i = "RCA Pending" Then

        If Msg = "" Then
         Msg = "Found 'RCA Pending' in cell" & " " & i.Address
        Else
         Msg = Msg & Chr(10) & "Found 'RCA Pending' in cell" & " " & i.Address
        End If

       End If
    Next i
    If Msg <> "" Then MsgBox Msg, vbExclamation, "Attention"
End Sub