One Message box

[Bomino]

Hello,
I currently have code that will display a specific cell value if criteria are met. This code will loop through, checking each criteria and produce a message displaying one cell value. Click ok, will loop again until another cell matches the criteria and produce another message.

What i'd like help with is for the routine to run, loop through all applicable cells, and produce a message at the end, displaying ALL cell values that match the criteria.

Private Sub Worksheet_Activate()
  Dim i As Variant
Dim FindRange As Range
Set FindRange = Range("E17:F21")

For Each i In FindRange

If i = 0 Then

MsgBox "For " & Range("E16").Value & vbNewLine & i.Offset(0, -1).Value & vbNewLine & "Has a Negative Variance", vbCritical, " Reports"

End If
Next i

End Sub

Thanks.

 

[SirJB7]

Hi, Bomino!

Try with this:

Option Explicit

Private Sub Worksheet_Activate()
    Dim I As Variant, A As String
    Dim FindRange As Range
    Set FindRange = Range("E17:F21")
    A = ""
    For Each I In FindRange
        If I = 0 Then
            If A <> "" Then A = A & vbNewLine
            A = A & "For " & Range("E16").Value & vbNewLine & I.Offset(0, -1).Value & vbNewLine & "Has a Negative Variance"
        End If
    Next I
    MsgBox A, vbCritical, " Reports"
    Set FindRange = Nothing
End Sub

Just advise if any issue.

Regards!

EDITED (SirJB7)
Comma duplicated in MsgBox statement.

 

[Smallman]

An alternative.

Option Explicit
 
Private Sub Worksheet_Activate()
Dim rng As Range
Dim txt As String
 
    For Each rng In [E17:F21]
        If rng = 0 Then
            txt = txt & " " & rng.Offset(, -1) & vbNewLine
        End If
    Next rng
    MsgBox "For " & [E16] & vbNewLine & txt & "Has a Negative Variance", vbCritical, "Reports"
 
End Sub

Take care

Smallman

 

[Bomino]

Hi,
Thank you guys for your prompt response.
From a newbie stand view, I think there is one comma too many in SirBJ's code line; as show below:

MsgBox A, , vbCritical, " Reports"

Smallman's code worked like a charm

Thanks and have a wonderful day!

 

[SirJB7]

@Bomino
Hi!
Comma problem fixed and post updated. Thank you.
Regards!

 

[Bomino]

@SirJB7

Thanks a lot sir!

 

[SirJB7]

Hi, Bomino!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!