Need Help counting rows

[TazRMan]

I need help with this problem. I have a column on sheet 1 that with has the following:

Sites

A
A
B
D
A
C
B
D

I need to count for each site and display it on sheet 2 using vba as shown.

Sites

A 3
B 2
C 1
D 2

I have the following code, but I know its going to need an array but I having trouble think on how it is done. Thanks for the help.

BTW the information will always start on row 5.

Sub test()
Dim i As Integer
Dim x As Integer
Dim c As Integer
Dim stName As String
c = 0
stName = Sheet1.Cells(5, 4).Value
i = Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row
For x = 5 To i
If stName = Sheet1.Cells(x, 4).Value Then

    c = c + 1
End If


Next x

End Sub

 

[Macropheliac]

Maybe you can adapt this:

Sub test()
Dim Tcell As Range
Dim Acount As Long
Dim Bcount As Long
Dim Ccount As Long
Dim Dcount As Long
Dim MySheet As Worksheet

Set MySheet = Sheets("Sheet2")

For Each Tcell In Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A65536").End(xlUp).Row)
        Select Case Tcell.Value
                Case "A"
                Acount = Acount + 1
                Case "B"
                Bcount = Bcount + 1
                Case "C"
                Ccount = Ccount + 1
                Case "D"
                Dcount = Dcount + 1
        End Select
Next Tcell

With MySheet
        .Range("A1").Value = "A" & Acount
        .Range("A2").Value = "B" & Bcount
        .Range("A3").Value = "C" & Ccount
        .Range("A4").Value = "D" & Dcount
End With
                

End Sub

Modify ranges and sheet names as necessary.

 

[SteveO59L]

Why not just use countif() ?

 

[TazRMan]

Thanks for the posting but the number of site will about 20+. I do not know the number of sites.

I know there is a way to do it in an array.

Also this must be done in VBA.