Search code examples
excelvbamsgbox

One MsgBox To show all passes and fails

I am trying to make one message box to include passes and fails of samples.

Data

All Ranges above 0.24 are a fail and below are a pass, and that to show in one box the passes and fails with correspondent sample #

The code below, show the boxes one by one and even incorrectly, some are blank and some not correct.

May you help me with this please. Thanks

Sub MsgB()
Dim x As Long
For x = 2 To 8
    If Sheet2.Range("B" & x).Value < 0.24 Then
       y = Sheet2.Range("A" & x).Value
      MsgBox "Pass: " & y
   Else
     MsgBox "Fail: " & y
   End If
Next

End Sub
Solution

  • You could accumulate the results in two string variables as shown below, and display the results after the loop has completed. Also, y is set only if the value is smaller than 0.24. You need to set y before the If.

    Sub MsgB()
Dim x As Long
Dim pass as String
pass = ""
Dim fail as String
fail = ""
For x = 2 To 8
    y = Sheet2.Range("A" & x).Value
    If Sheet2.Range("B" & x).Value < 0.24 Then
        pass = pass & ", " & y
    Else
        fail = fail & ", " & y
    End If
Next
' Print pass and fail, removing the leading ", ".
pass = Right(pass, Len(pass) - 2)
fail = Right(fail, Len(fail) - 2)
MsgBox "Pass: " & pass & vbCrLf & "Fail: " & fail

    End Sub