Help counting values using VBA

[ola_f]

Hi i have xl file that contains a two tables of data. (see below) One table contains the names of some datahubs and the other contains sites that connects to the hubs.
Wat i would like to do is to Count the number of sites each datahub has and store that value so it could be reused for other calculations in the vba as showed below. There will never be more then 15 hubs but it could be like 200 sites connecting to diffrent hubs. I have tried to find some thing that will work for this but with out luck.

// OF

[TABLE="width: 500"]
<tbody>[TR]
[TD]Hub
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hub1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hub2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hub3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sites
[/TD]
[TD]Hub
[/TD]
[/TR]
[TR]
[TD]Site 1
[/TD]
[TD]Hub 1
[/TD]
[/TR]
[TR]
[TD]Site 2
[/TD]
[TD]Hub 1
[/TD]
[/TR]
[TR]
[TD]Site 3
[/TD]
[TD]Hub 2
[/TD]
[/TR]
[TR]
[TD]Site 4
[/TD]
[TD]Hub 3
[/TD]
[/TR]
[TR]
[TD]Site 5
[/TD]
[TD]Hub 2
[/TD]
[/TR]
[TR]
[TD]Site 6
[/TD]
[TD]Hub 3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Dim dDegreeInc As Double
Dim dRad As Double


For a = 0 To UBound(here should the data storing number of sites connected to a specific hub be placed)

dDegreeInc = 360 / a
dRad = (dDegreeInc * a) * PI / 180

 

[Marcelo Branco]

Maybe you can use a Dictionary to store the count for each Hub

Assuming your data in Sheet1 like below (adjust)


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

Sites​

[/td][td]

Hub​

[/td][/tr]
[tr][td]

2​

[/td][td]

Site 1​

[/td][td]

Hub 1​

[/td][/tr]
[tr][td]

3​

[/td][td]

Site 2​

[/td][td]

Hub 1​

[/td][/tr]
[tr][td]

4​

[/td][td]

Site 3​

[/td][td]

Hub 2​

[/td][/tr]
[tr][td]

5​

[/td][td]

Site 4​

[/td][td]

Hub 3​

[/td][/tr]
[tr][td]

6​

[/td][td]

Site 5​

[/td][td]

Hub 2​

[/td][/tr]
[tr][td]

7​

[/td][td]

Site 6​

[/td][td]

Hub 3​

[/td][/tr]
[tr][td]

8​

[/td][td]

Site 7​

[/td][td]

Hub 2​

[/td][/tr]
[tr][td]

9​

[/td][td]

Site 8​

[/td][td]

Hub 2​

[/td][/tr]
[tr][td]

10​

[/td][td]

Site 9​

[/td][td]

Hub 2​

[/td][/tr]
[tr][td]

11​

[/td][td]

Site 10​

[/td][td]

Hub 2​

[/td][/tr]
[/table]

Try this

Sub CountSites()
    Dim lastRow As Long, rCell As Range
    Dim dict As Object, strHubName
    
    Set dict = CreateObject("Scripting.Dictionary")
    dict.comparemode = vbTextCompare
    
    With Sheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                
        For Each rCell In .Range("B2:B" & lastRow)
            If dict.exists(rCell.Value) Then
                dict.Item(rCell.Value) = dict.Item(rCell.Value) + 1
            Else
                dict.Add rCell.Value, 1
            End If
        Next rCell
    End With
    
    'Print some values for testing
    strHubName = "Hub 1"
    Debug.Print strHubName & " has " & dict.Item(strHubName) & " sites"
    strHubName = "Hub 2"
    Debug.Print strHubName & " has " & dict.Item(strHubName) & " sites"
    
End Sub

Hope this helps

M.