Q:

Find column with name "x" and count word occurrences in that column

I have a worksheet of data with headers.  I am trying, in VBA, to find the column with the header "type" and then in that column count the amount of times string "x" appears, i.e count the number of times "add" appears in column with header "type".
I know you can create a scripting dictionary to count the amount of times each word appears, I am having issues with searching through the headers to find the column "type".
My code so far looks at every cell in the sheet however i just want to limit it to the column "type":
Dim shtSheet1 As String
Dim dict As Object
Dim mycell As Range

shtSheet1 = "Test"   

Set dict = CreateObject("Scripting.Dictionary")

dict.Add "Add", 0
dict.Add "Delete", 0
dict.Add "Update", 0

For Each mycell In ActiveWorkbook.Worksheets(shtSheet1).UsedRange
If dict.Exists(ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value) Then
        dict(ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value) = dict(ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value) + 1
End If
Next

Thanks for any help!

A:

if I understood correctly, then you can use this:
Sub test()
    Dim Dict As Object: Set Dict = CreateObject("Scripting.Dictionary")
    Dim shtSheet1 As Worksheet: Set shtSheet1 = Sheets("Test")
    Dim mycell As Range, n&, z&
    Dim Fx As Object, Key As Variant
    Set Fx = WorksheetFunction
    Dict.CompareMode = vbTextCompare
    With shtSheet1
        n = .Rows(1).Find("Type").Column
        z = .Cells(.Rows.Count, n).End(xlUp).Row
        For Each mycell In .Range(.Cells(2, n), Cells(z, n))
            If Not Dict.Exists(Fx.Trim(mycell)) Then Dict.Add Fx.Trim(mycell), 0
        Next
        For Each mycell In .Range(.Cells(2, n), Cells(z, n))
            If Dict.Exists(Fx.Trim(mycell)) Then
                 Dict(Fx.Trim(mycell)) = CLng(Dict(Fx.Trim(mycell))) + 1
            End If
        Next
    End With
    For Each Key In Dict
        Debug.Print Key, Dict(Key)
    Next Key
End Sub

output with data example is below:

update
variant using worksheetfunction.countif with dictionary
Sub test2()
    Dim Dict As Object: Set Dict = CreateObject("Scripting.Dictionary")
    Dim shtSheet1 As Worksheet: Set shtSheet1 = Sheets("Test")
    Dim mycell As Range, n&, Data As Range
    Dim Fx As Object, Key As Variant
    Set Fx = WorksheetFunction
    Dict.CompareMode = vbTextCompare
    With shtSheet1
        n = .Rows(1).Find("Type").Column
        Set Data = .Range(.Cells(2, n), Cells(.Cells(.Rows.Count, n).End(xlUp).Row, n))
        For Each mycell In Data
            If Not Dict.Exists(Fx.Trim(mycell)) Then Dict.Add Fx.Trim(mycell), Fx.CountIf(Data, "*" & Fx.Trim(mycell) & "*")
        Next
    End With
    For Each Key In Dict
        Debug.Print Key, Dict(Key)
    Next Key
End Sub

