Counting exception number and pop up in MSG BOX

[Elton Law]

Dear Expert,
Have run a marco and the final outcome is as follows:

Marks OK/Fail
10 Fail
9 Fail
19 OK
27 OK
21 OK
0 Fail
#N/A #N/A

Is it possible add certain VB scripts at the end of my existing marco so
that it can pop up a MSGBOX saying "there is 1 exception (becasue there is
one invalid data) and there are 3 Fails (beacasue there are 3 Fails)" please ?
Thanks,
Elton

 

[AltaEgo]

This should do it for you - I'll leave you fine-tune the wording in the
message box.

Sub CountResults()

Dim ResultF
Dim ResultOK
Dim ResultNA

ResultF = WorksheetFunction.CountIf(Range("yourRange"), "Fail")
ResultOK = WorksheetFunction.CountIf(Range("yourRange"), "OK")
ResultNA = WorksheetFunction.CountIf(Range("yourRange"), "#N/A")


MsgBox ResultNA & " Exceptions" & vbCrLf & ResultF & " Fail" & vbCrLf &
ResultOK & " OK"


End Sub

 

[Elton Law]

Hi Altaego,

I copy and paste your scripts.

Error immediately popped up and turn RED.

MsgBox ResultNA & " Exceptions" & vbCrLf & ResultF & " Fail" & vbCrLf &
ResultOK & " OK"

Can you help please ?
Thanks

 

[OssieMac]

Slightly different code that accomodates all types of errors not just #N/A.
Also a little more generic in that it accommodates changinf number of rows in
the column.

Also when lines turn red it is usually due to the line breaking in the post.
Just edit and bring it all up on to one line. I usually insert line breaks to
prevent this occurring.

A space and an underscore at the end of a line is a line break in an
otherwise single line of code.

Sub MsgBoxReport()

Dim lngOK As Long
Dim lngFail As Long
Dim lngErrors As Long
Dim rngOK_Fail As Range
Dim c As Range

With Sheets("Sheet1")
'Edit "B" (twice) in folowing to your required column
Set rngOK_Fail = .Range(.Cells(2, "B"), _
.Cells(.Rows.Count, "B").End(xlUp))
End With

lngOK = WorksheetFunction.CountIf(rngOK_Fail, "OK")

lngFail = WorksheetFunction.CountIf(rngOK_Fail, "Fail")

For Each c In rngOK_Fail
If IsError(c.Value) Then
lngErrors = lngErrors + 1
End If
Next c

MsgBox "OK = " & lngOK & vbCrLf & _
"Fail = " & lngFail & vbCrLf & _
"Errors = " & lngErrors


End Sub

 

[Elton Law]

Hi OssieMac, that's really work. Thanks. I now know what is the meaning of
Superman !!!
Thanks indeed.

 

[AltaEgo]

The problem is caused by the line of code wrapping.

Just place your cursor at the end of the line that ends

vbCrLf &

and press [Delete]

until it pulls up

ResultOK & " OK"


So, the end of the offending line should now read:

vbCrLf & ResultOK & " OK"

 

[AltaEgo]

However, since OssieMac went the extra yard to check for all errors instead
of NA, that code may be more robust for you.

--
Steve

The problem is caused by the line of code wrapping.

Just place your cursor at the end of the line that ends

vbCrLf &

and press [Delete]

until it pulls up

ResultOK & " OK"


So, the end of the offending line should now read:

vbCrLf & ResultOK & " OK"

--
Steve

Hi Altaego,

I copy and paste your scripts.

Error immediately popped up and turn RED.

MsgBox ResultNA & " Exceptions" & vbCrLf & ResultF & " Fail" & vbCrLf &
ResultOK & " OK"

Can you help please ?
Thanks