CODEWITHSUNDEEP
excelvba
SimpleK
SimpleK

Reputation: 11

VBA Excel, Grouping and then counting

I'm not sure if I'm in the right direction or going the complete opposite, but I want to Group Data by a Variant, and then count it. For example,

    Dim Fruit As Variant
Fruit = Array("Apple", "Grape", "Lemon", "Melon", "Orange")

Then I want to search/find a column for anything that matches "Fruit" and then count all the matches.

In the excel, I'm focusing on Column D

Example

Upvotes: 0

Views: 313

Answers (1)

Darrell H
Darrell H

Reputation: 1886

There may be a more efficient way to do it, but this works...

Sub CountFruit()

    Dim Fruit As Variant
    Dim LR As Integer
    Dim t As Integer
    Dim g As Integer

    Fruit = Array("Apple", "Grape", "Lemon", "Melon", "Orange")

    LR = Cells(Rows.Count, 4).End(xlUp).Row

    t = 0

    For x = 2 To LR
        For g = LBound(Fruit) To UBound(Fruit)

            If Cells(x, 4).Value = Fruit(g) Then
                t = t + 1
            End If

        Next g
    Next x

    NumFruit = t

End Sub

Upvotes: 1

Related Questions