How to get the count of a perticular object by checking the two conditions

Question

I have to write a macro for the following scenario:

I have a excel book having one sheet. Sheet contains the module name with test case and test status. I have to count the test case with the test status for a particular module say "Mod1".

Module    Test Case ID    Status
Mod1            123         Pass
Mod2            124         Fail
Mod1            125         Fail
Mod1            126         Blocked
Mod5            127         Pass
Mod1            128         NA

the code i have written is as below:

Sub testnw()

Dim k, l, ps, fl, bl, na As Integer

Dim frng As Range


ps = 0
fl = 0
bl = 0
na = 0
frng = Worksheets(1).Range("A1")


k = 1
l = 1
       While (frng.Cells(k, l).Value <> "")

        If frng.Cells(k, l).Value = "Mod1" Then
            If frng.Cells(k, l + 2).Value = "Pass" Then
            ps = ps + 1
            ElseIf frnng.Cells(k, l + 2).Value = "Fail" Then
            fl = fl + 1
            ElseIf frng.Cells(k, l + 2).Value = "Blocked" Then
            bl = bl + 1
            Else
            na = na + 1
            End If
        End If
    Wend
MsgBox (ps)
MsgBox (fl)
MsgBox (bl)
MsgBox (na)
End Sub

on running the above code I am getting an error message is that "Object Variable or With Block variable not set".

Can anybody tell me where I am wrong and get me the correct code?

Thanks in advance.

do you need to do it in VBA? If not, a pivot table will help you get the job done in a few seconds: insert a pivot table based on your sheet. place "Module" in the row fields, "Status" in the column field and "Test Case ID" in the value field. If the test case are numeric, you need to change the aggregation method from SUM to COUNT. Done! :-) — Peter Albert, Feb 27 '13 at 20:21
Note that `k`, `l`, `ps`, `fl` and `bl` have been declared as `Variant`. To declare them all as integers requires: `Dim k As Integer, l As Integer` and so on — barrowc, Feb 27 '13 at 23:19
Thanks Peter...-But I have to do this with excel macro. I using three different sheet to pick the data. This just i was trying to check the code by putting the other excel data in same excel sheet. :) — Brijesh Agrawal, Feb 28 '13 at 06:28

score 1 · Accepted Answer · edited May 23 '17 at 11:56

1

the error is in this line:

frng = Worksheets(1).Range("A1")

as you are using an Object (not a simple variable), you need a SET command:

set frng = Worksheets(1).Range("A1")

see this question for more information on LET and SET

edited May 23 '17 at 11:56

Community

1
1

answered Feb 27 '13 at 20:30

SeanC

15,695
5
45
66

@ Sean...I used the SET command but still i am not getting the proper output, it's giving me "0" as output for all the variable. – Brijesh Agrawal Feb 28 '13 at 07:21
have you single stepped though the code to see where it drops out of the loop? (F8) Also, have you put `Option Explicit` to pick up spelling mistakes? – SeanC Feb 28 '13 at 13:06

score 1 · Answer 2 · answered Feb 27 '13 at 22:07

As Peter Albert suggested, you really should use a Pivot Table for this. There's no sense in recreating the wheel, unless you have very specific needs for doing this in VBA.

If you're curious what this looks like in a Pivot Table, here's how to go about creating one:

1. Highlight the range of data (Module, Test Case ID, Test Status)

2. Go to Insert -> Pivot Table, then click OK

enter image description here

3. On the right under PivotTable Field List, drag:

Module to Row Labels,
Test Case ID to Values
Status to Column Labels

enter image description here

Here's what the resulting table would look like:

enter image description here

If this isn't the exact view you were looking for, you can play with the PivotTable Field List values (e.g. switch Status and Test Case ID) until you find the one that suits your needs.

How to get the count of a perticular object by checking the two conditions

2 Answers2