CODEWITHSUNDEEP
excelvba
Iron Man
Iron Man

Reputation: 849

Combine Multiple MsgBox into one

When we open this workbook, this code will look through a range and find cells that contain the text "RCA Pending" and will popup a MsgBox letting the user know which row in that column contains the specific text. The problem is, if there are multiple rows containing this text, there will also be multiple MsgBox popups.

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

What needs to be changed in this code so that there is only one popup that lists all the rows where "RCA Pending" was found?

Upvotes: 1

Views: 2486

Answers (2)

Fadi
Fadi

Reputation: 3322

Try this code:

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

Upvotes: 3

Davesexcel
Davesexcel

Reputation: 6984

Try this,

Sub Msgbox_It()

    Dim sh As Worksheet
    Dim LstRw As Long
    Dim i As Range
    Dim FindRange As Range
    Dim Msg As String

    Set sh = Sheets("Sheet1")    'name of worksheet
    With sh
        LstRw = .Cells(.Rows.Count, "AB").End(xlUp).Row
        Set FindRange = .Range("AB2:AB" & LstRw)

        For Each i In FindRange
            If i = "RCA Pending" Then
                Msg = Msg & i.Address & vbNewLine
            End If
        Next i

        MsgBox "Found 'RCA Pending' in cell" & " " & Msg, vbExclamation, "Attention"
    End With
End Sub

Upvotes: 2

Related Questions