Count multiple numbers in the same column

cobber1418 · Aug 27th 2020

Hi, can the below code be modified to change "UW" to count (not sum) how many times 0, 1, 2, 3 appear in the same column. I think KjBox originally gave me the code, cheers.

Code

Private Sub Total_UW()
    Dim x, y, Rws, ContAreas, i As Long, ii As Long
    Const Category As String = "Yes"
    Const Status As String = "UW" 'CAN THIS BE MODIFIED TO COUNT (NOT SUM) MULTIPLE NUMBERS IN THE SAME COLUMN IE 0 1 2 3 *****************
    Rws = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18)
    ContAreas = Array("WSCA1", "WSCA2", "SECA11", "SECA12", "SECA13", "NWCA5", "NWCA6A", _
                    "NWCA6B", "NWCA7", "NWCA8", "NWCA9", "NWCA10A", "NWCA10B", _
                    "WSCA3", "WSCA4", "SECA14", "SECA15", "SECA16")
    x = Sheets("calculations").[a6].CurrentRegion
    ReDim y(1 To 18, 1 To 1)
    For i = 2 To UBound(x, 1)
        If x(i, 3) = Category And x(i, 5) = Status Then
            For ii = LBound(ContAreas) To UBound(ContAreas)
                If ContAreas(ii) = x(i, 2) Then y(Rws(ii), 1) = y(Rws(ii), 1) + 1
            Next
        End If
    Next
    With ActiveSheet
        .[d5].Resize(18) = y
        .Activate
    End With
End Sub

Display More

Justin Doward · Aug 31st 2020

Is the intent to find out if x(i, 5) = the number of numeric in the chosen column?

It is easy enough, but difficult from you question to ensure that is what you are after.

if so you would do something like replace:

Code

Const Status As String = "UW"

with

Code

dim UW as long
UW = Application.WorksheetFunction.Count(Sheet1.Range("B1:B10"))

Count multiple numbers in the same column

Participate now!

Share

Tags