Excel vba function with dynamic table column as parameter

Question

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.

The headers are the default ones when I create a table (Column1, Column2, etc) — Enorio
– Enorio, Commented Nov 10, 2018 at 18:03

QHarr · Accepted Answer · 2018-11-10 19:15:57Z

1

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:

edited Nov 10, 2018 at 19:15

answered Nov 10, 2018 at 18:11

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Enorio Over a year ago

And how do i call this function? In a cell i write =GetCount(foo;"Column1","Value")

QHarr Over a year ago

TBH you can just put =COUNTIF(foo[[#All],[Column1]],"A") and change the Column1 and A as required.

QHarr Over a year ago

And yes I have edited so you can call as =GetCount(foo,"Column1","Value")

Enorio Over a year ago

The getcount was not working, couldn't found the table. Apparently is just =CONTAR.SE(Tabela1[Coluna6];"A") and it works. It's in PT the excel. Thanks for the help, as you can see, i'm worse than a beginner :)

Collectives™ on Stack Overflow

Excel vba function with dynamic table column as parameter

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related