Vfrdtyky

I'm trying to do an excel that counts the number of times that a certain letter appears in a table column.

What I'm trying to do is to create a dynamic table, in which i can always add new lines. Because of that, i need a function that has as a parameter one of the columns, which counts the number of times that another parameter appears.

Example, count the number of "shift" in the "column" (:

Function sumColumnShifts(column As Integer, shift As range) As Integer

    sumColumnShifts = sumDayShifts(ActiveSheet.ListObjects("foo").ListColumns(column).range.Select, shift)

End Function



Public Function sumDayShifts(ByVal Target As range, shift As range) As Variant

    Dim res As Integer

    res = 0

    For Each cell In Target    

        If shift.cells(1, 1).Value = cell.Value Then

            res = res + 1

        End If

    Next

    sumDayShifts = res

End Function

The problem here is the function can't find the table, but the table exists.
What am I doing wrong?
Is it the ActiveSheet.ListObjects("foo").ListColumns(column).range.Select?
This is not a range? I can't access this in a function?

Thanks.

Can't you just use

=COUNTIF(foo[[#All],[Column1]],"A")

Otherwise,

I would pass the ListObject name and header of column to search along with the search value and use Countif in the function to return the count. You could also alter the function to pass the worksheet as an argument to the function to make it more flexible.

Option Explicit   

Public Sub Test()



    Const SEARCH_HEADER As String = "Column1"

    Const SEARCH_VALUE As String = "A"

    Const TABLE_NAME As String = "foo"



    Debug.Print GetCount("foo", SEARCH_HEADER, SEARCH_VALUE)

End Sub



Public Function GetCount(ByVal tableName As String, ByVal searchHeader As String, ByVal searchValue As String) As Variant

    Dim ws As Worksheet, table As ListObject

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    On Error Resume Next

    Set table = ws.ListObjects("foo")

    On Error GoTo 0

    If table Is Nothing Or IsError(Application.Match(table.HeaderRowRange, searchHeader, 0)) Then

        GetCount = CVErr(xlErrNA)

        Exit Function

    End If



    GetCount = Application.WorksheetFunction.CountIf(table.ListColumns(searchHeader).DataBodyRange, searchValue)

End Function

Data: