Need Help counting rows

TazRMan

New Member
Joined
Sep 28, 2005
Messages
22
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.

Code:
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165
Maybe you can adapt this:
Code:
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.
 

TazRMan

New Member
Joined
Sep 28, 2005
Messages
22
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.
 

Forum statistics

Threads
1,137,208
Messages
5,680,196
Members
419,889
Latest member
ballsofspartans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top