Question about checking for a certain string then counting the number of times it appears in a given range. (VBA)

[simplychelsea9]

Hello! I am working on a project that is totaling overall values over a certain period, but I am having trouble with the last part, which is finding certain strings in a given range, then counting how many times they appear.

For example, I have this range of data in an excel sheet:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Action
[/TD]
[TD]Date
[/TD]
[TD]TargetID
[/TD]
[TD]ActionMessage
[/TD]
[/TR]
[TR]
[TD]Reset
[/TD]
[TD]10/14/2014
[/TD]
[TD]company.net
[/TD]
[TD]Failure:AccessDenied
[/TD]
[/TR]
[TR]
[TD]Reset
[/TD]
[TD]10/14/2014
[/TD]
[TD]company.net
[/TD]
[TD]Success
[/TD]
[/TR]
[TR]
[TD]Reset
[/TD]
[TD]10/14/2014
[/TD]
[TD]contractor.net
[/TD]
[TD]Success
[/TD]
[/TR]
[TR]
[TD]Reset
[/TD]
[TD]10/14/2014
[/TD]
[TD]consultant.net
[/TD]
[TD]Failure:PasswordError
[/TD]
[/TR]
[TR]
[TD]Reset
[/TD]
[TD]10/14/2014
[/TD]
[TD]company.net
[/TD]
[TD]Failure:AccessDenied
[/TD]
[/TR]
</tbody>[/TABLE]











So let's say in cell F13, if I am checking the range for error messages in the ActionMessage Column and want to place the result in F13, I need it to appear like:

Failure: Access denied (2)
Failure: Password Error (1)

Alternatively, if Success if found, I don't want it to list anything.

I don't even know how to begin though.. Do I make an array and then use countif statements? A little push in the right direction would be greatly appreciated because I am completely lost right now. Thanks!

 

[tlowry]

Hey! welcome to MrExcel.

Below is a bit of VBA code. It uses two concepts:

1. Defining a range:

Set rng = ActiveSheet.Range("D2:D" & Cells(Rows.Count, 4).End(xlUp).Row)

2. Using a Dictionary:

Set dict = CreateObject("Scripting.Dictionary")

There are other ways of counting occurrences, but I find that the Dictionary Object keeps track of the arrays it uses (keys and items) well and has functions such as: Exists, Add (and others). So, I suggest you spend a bit of time with the Dictionary object.

Sub DoTheCount()
    Dim rng: Set rng = ActiveSheet.Range("D2:D" & Cells(Rows.Count, 4).End(xlUp).Row)
    Dim cCell, dkey, i
    Dim dict: Set dict = CreateObject("Scripting.Dictionary")
    For Each cCell In rng.Cells
        If InStr(cCell, "Failure") > 0 Then
            If dict.exists(cCell.Text) Then
                dict.Item(cCell.Text) = dict.Item(cCell.Text) + 1
            Else
                dict.Add cCell.Text, 1
            End If
        End If
    Next cCell
    i = 0
    For Each dkey In dict.keys
        Range("F13").Offset(i, 0) = dkey & " (" & dict.Item(dkey) & ")"
        i = i + 1
    Next
End Sub