Need help in counting

[rainx]

Sample Data: I have tis set of sample data that I have already managed to get the names counted in each class using vba code as shown below. Now I need to count those names that belongs to the singapore branch. I have done a match with my namelist using MATCH in column E, if return a number, it means a match wich means the person belongs to singapore brach. So now I need to count those names who belongs to SIngapore branch in each class. Please help thanks!!

Sub Count()
Dim oneCell As Range
With ThisWorkbook.Sheets("sheet1").Range("A:A")
    With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        .Offset(.Rows.Count, 0).Range("A1:A2") = [{"x";"---"}]
        With .Offset(0, 1)
            .Cells(1, 1).FormulaArray = _
                  "=IF((LEN(SUBSTITUTE(R[1]C1,""-"",""""))<(LEN(R[1]C1)-1)),SMALL(IF(LEN(RC1:R[99]C1)-LEN(SUBSTITUTE(RC1:R[99]C1,""-"",""""))>1,ROW(RC1:R[99]C1),9999),2) - SMALL(IF(LEN(RC1:R[99]C1)-LEN(SUBSTITUTE(RC1:R[99]C1,""-"",""""))>1,ROW(RC1:R[99]C1),9999),1)-3,"""")"
            .FillDown
            .Value = .Value
        End With
        .Offset(.Rows.Count, 0).Range("A1:A2").ClearContents
        On Error Resume Next
        For Each oneCell In .SpecialCells(xlCellTypeBlanks)
            If oneCell.Offset(-3, 1).Value Then oneCell.Offset(-3, 1).Value = 0
        Next oneCell
        With .Offset(0, 1).Cells(.Rows.Count, 1).End(xlUp)
            If .Value < 0 Then .Value = 0
        End With
        On Error GoTo 0
    End With
End With
End Sub

<table x:str="" style="border-collapse: collapse; width: 492pt;" width="655" border="0" cellpadding="0" cellspacing="0"><col style="width: 259pt;" width="345"> <col style="width: 69pt;" width="92"> <col style="width: 62pt;" width="83"> <col style="width: 49pt;" width="65"> <col style="width: 53pt;" width="70"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl28" style="height: 12.75pt; width: 259pt;" width="345" height="17">A</td> <td class="xl28" style="width: 69pt;" width="92">B</td> <td class="xl28" style="width: 62pt;" width="83">C</td> <td class="xl28" style="width: 49pt;" width="65">D</td> <td class="xl28" style="width: 53pt;" width="70">E</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 259pt;" width="345" height="17">Class 1</td> <td class="xl24" style="border-left: medium none; width: 69pt;" width="92">Instructor: </td> <td class="xl24" style="border-left: medium none; width: 62pt;" x:str="Tom " width="83">Tom </td> <td class="xl24" style="border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" x:str="02/11/2008 - 02/13/2008 8:30 AM - 5:00 PM " width="345" height="17">02/11/2008 - 02/13/2008 8:30 AM - 5:00 PM </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">Room:</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83">Singapore</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Calvin</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">57725</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">tom</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">59085</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">mary</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">1486</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">david</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">1605</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">James</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">1658</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Phillip</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">2911</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Grace</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60567</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Kiro</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">CFN</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60202</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Adam</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">58876</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Rain</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60042</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Class 2</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">Instructor: </td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83">Ryan</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" x:str="04/14/2008 - 04/16/2008 8:30 AM - 5:00 PM " width="345" height="17">04/14/2008 - 04/16/2008 8:30 AM - 5:00 PM </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">Room:</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" x:str="Singapore " width="83">Singapore </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Calvin</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">21385</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">31</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">tom</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">57643</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">38</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">mary</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">58949</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">55</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">david</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">57547</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">58</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">James</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">57565</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">66</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Phillip</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">58477</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">72</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Grace</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">58149</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">75</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Kiro</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60859</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">82</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Adam</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">20248</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">87</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Rain</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60902</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Rachel</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60169</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">108</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Paulyn</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">59656</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">118</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">adeline</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60810</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">122</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">catherine</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">59249</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">126</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">charles</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60133</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">128</td> </tr> </tbody></table>

 

[jindon]

try

Sub myCount()
Dim myAreas As Areas, myArea As Range
With Range("e4", Range("e" & Rows.Count).End(xlUp))
    On Error Resume Next
    Set myAreas = .SpecialCells(2).Areas
    On Error GoTo 0
    If myAreas Is Nothing Then Exit Sub
    For Each myArea In myAreas
        myArea.Cells(1).Offset(-3, -3).Value = Application.Count(myArea)
    Next
End With
End Sub

 

[rainx]

Hmm nth comes out when i run it? I need to count how many locals in each class...

Thanks alot!

 

[jindon]

It should count and replace "Instructor:" in Col.B with numbers

 

[rainx]

It is not working, izzit because in column E i did the match then those that dun match it will return a #N/A error ? thats y it is not able to count? what can i do abt it?

Thanks alot !

 

[jindon]

OK
change

Set myAreas = .SpecialCells(2).Areas

to

Set myAreas = .SpecialCells(-4123).Areas

 

[rainx]

it can run now, but then can only return 1 number at the 1st instructor cell, I think it counted all instead of in classes.

So sorry..

But thanks alot, really appreciate the help!

 

[jindon]

then try

Sub myCount()
Dim myAreas As Areas, myArea As Range
With Range("a4", Range("a" & Rows.Count).End(xlUp))
    On Error Resume Next
    Set myAreas = .SpecialCells(2).Areas
    On Error GoTo 0
    If myAreas Is Nothing Then Exit Sub
    For Each myArea In myAreas
        myArea.Cells(1).Offset(-3, 1).Value = Application.Count(myArea.Offset(, 4))
    Next
End With
End Sub

 

[rainx]

Hi

I have managed to resolve it with the previous code with some amendments to my own formula in column E.

Thanks alot!