Counting Multiple instances

[Frostrunr]

Hello,

I'm trying to figure out the code for counting multiple instances of different text in a column and displaying it all under one msgbox as a summary.

So far I have only been able to get one count going with the below code;

Sub Count_Instances()
Dim instances As Long

instances = WorksheetFunction.CountIf(Columns("A"), "RED")

If instances <> 0 Then _
MsgBox "Found " & instances & " RED", vbInformation, "RED"
End Sub

However I want it to check for 6 different colors count how many instances there are of each and display them as a summary on one message box.
i.e
7 x Red
4 x Green
3 x Blue
1 x Yellow
4 x Pink
2 x Black

Sorry if its a massive ask :D I've been puzzling my way trying to figure it out all week, starting to think its near impossible. As I'm sure everyone does when trying to figure out VBA Code :D

Any Guidance or ideas would be greatly appreciated.

 

[Marcelo Branco]

Maybe something like this...

Sub aTest()
    Dim arrColors As Variant, instances As Long
    Dim msg As String, i As Long
    
    arrColors = Array("Red", "Green", "Blue", "Yellow", "Pink", "Black")
    For i = LBound(arrColors) To UBound(arrColors)
        instances = Application.CountIf(Columns("A"), arrColors(i))
        If instances > 0 Then msg = msg & instances & " x " & arrColors(i) & vbNewLine
    Next i
    MsgBox msg, Title:="Colors Count"
End Sub

Hope this helps

M.

 

[Frostrunr]

that worked perfectly thank you so much !!!!!!!!!

 

[Marcelo Branco]

You're welcome. Glad to help

M.