VBA Countifs - refer to table column as variable

[zico8]

Hi,

How to refer Application.WorksheetFunction to the variable which represents column of table?

This macro works perfect:

Public MyTable as Object
Sub test ()
Set MyTable = ThisWorkbook.Worksheets("Sheet1").ListObjects("MyTable_table")
Msgbox Application.WorksheetFunction.CountIf(MyTable.ListColumns("Column1").DataBodyRange, "John")
End Sub

whilst that does not:

Public MyTable as Object
Sub test ()
Set MyTable = ThisWorkbook.Worksheets("Sheet1").ListObjects("MyTable_table")
MyColumn = MyTable.ListColumns("Column1").DataBodyRange
Msgbox Application.WorksheetFunction.CountIf(MyColumn, "John")
End Sub

Why?

 

[FormR]

Hi, try like this, for example:

Sub test()
Dim MyColumn As Range
Set MyTable = ThisWorkbook.Worksheets("Sheet1").ListObjects("MyTable_table")
Set MyColumn = MyTable.ListColumns("Column1").DataBodyRange
MsgBox Application.WorksheetFunction.CountIf(MyColumn, "John")
End Sub

And it would probably be better to declare MyTable as ListObject.

Public MyTable As ListObject

 

[zico8]

Thanks,

But what is a difference?

 

[FormR]

The Countif() function is expected a range object in the first argument, you were passing (an empty) variant.