Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exception from HRESULT: 0x800A03EC - COM Exception Unhandled | Excel Programming | |||
Counting number of cells that match name AND number | Excel Worksheet Functions | |||
Counting the number when you have a number and text | Excel Worksheet Functions | |||
Finding a maximum number...with an exception | Excel Discussion (Misc queries) | |||
Number Counting | Excel Discussion (Misc queries) |