unable to get the countif property of the worksheetfunction class

Question

I'm trying to check if a cell value exists in another range using countIf but i got the error above, i've tried to loop though the range to look for the value but didnt work

I also tried to convert both my column's format before countIf

    With col_member_id
        .NumberFormat = "General"
        .Value = .Value
    End With

    With col_remarks
        .NumberFormat = "General"
        .Value = .Value
    End With

So here's my code

Sub CheckValue()

    Dim tbl_data As ListObject, _
        tbl_member As ListObject, _
        col_member_id As Range, _
        col_id As Range, _
        col_done_by As Range, _
        col_remarks As Range, _
        col_type As Range, _
        col_amount As Range, _
        col_payment As Range

    Set tbl_data = ActiveWorkbook.Worksheets("Sheet1").ListObjects("Data")
    tbl_data.ListColumns.Add.Name = "Payment"

    Set tbl_member = ActiveWorkbook.Worksheets("Member Names").ListObjects("tbl_member")
    Set col_member_id = tbl_member.ListColumns("MEMBER ID").Range
    Set col_id = tbl_data.ListColumns("IDs").Range
    Set col_done_by = tbl_data.ListColumns("Done By").Range
    Set col_remarks = tbl_data.ListColumns("Remarks").Range
    Set col_type = tbl_data.ListColumns("Type").Range
    Set col_amount = tbl_data.ListColumns("Amount").Range
    Set col_payment = tbl_data.ListColumns("Payment").Range

    For i = 1 To col_id.Cells.Count + 1

        If (Cells(i, col_done_by.Column).Value = "gg1") And _
            (Cells(i, col_amount.Column).Value > 0) And _
            (Application.WorksheetFunction.CountIf(col_member_id, Cells(i, col_remarks.Column).Value) > 0) And _
            ((Cells(i, col_type.Column).Value = "ww") Or (Cells(i, col_type.Column).Value = "tt")) Then
            Cells(i, col_payment.Column).Value = "gg1 done"
        End If

    Next i

End Sub

You'd get that error if the function is returning an error value. — Rory, Jun 04 '19 at 11:26
@Rory How can i check what caused the error? because some row do get the correct result, but i dont know which row caused error. jsheeran, i got it from [here](https://stackoverflow.com/a/29670546/11498207) — espifi059 espifi059, Jun 04 '19 at 11:54
When you get the error, click Debug and check the value of `i`. Perhaps you are looking up an error value. — Rory, Jun 04 '19 at 11:57
Probably at any moment in your loop `Cells(i, col_remarks.Column).Value` returns an error value. Check the column `MEMBER ID` and make sure all cells throw a value and none of them shows any error — Foxfire And Burns And Burns, Jun 04 '19 at 12:21
@Rory,@Foxfire i found the line that caused error, after delete the value from `col_remarks` it works fine, but i dont know why that value caused arror. — espifi059 espifi059, Jun 04 '19 at 12:34

unable to get the countif property of the worksheetfunction class

0 Answers0