Counting exception number and pop up in MSG BOX
 

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

Counting exception number and pop up in MSG BOX
 

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


--
Steve

"Elton Law" wrote in message
...
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

Counting exception number and pop up in MSG BOX
 

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


"AltaEgo" wrote:

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


--
Steve

"Elton Law" wrote in message
...
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

Counting exception number and pop up in MSG BOX
 

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

--
Regards,

OssieMac

Counting exception number and pop up in MSG BOX
 

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

"OssieMac" wrote:

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

--
Regards,

OssieMac

Counting exception number and pop up in MSG BOX
 

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

"Elton Law" wrote in message
...
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


"AltaEgo" wrote:

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


--
Steve

"Elton Law" wrote in message
...
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

Counting exception number and pop up in MSG BOX
 

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

--
Steve

"AltaEgo" <Somewhere@NotHere wrote in message
...
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

"Elton Law" wrote in message
...
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


"AltaEgo" wrote:

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


--
Steve

"Elton Law" wrote in message
...
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