WorksheetFunction.CountA not returning correct value

1
votes

What I am trying to do is iterate through a range containing worksheet names, and if the cell is not empty then add the result of CountA function to the count variable.

So the count variable should be equal to number of non-blank cells in range B9:B28 on the worksheets I'm iterating through, but strangely the value is equal to the number of non empty cells in the range I'm going through (sheet1!d5:d24).

What am I doing wrong? Here's the code I am using:

For Each c In Worksheets("Sheet1").Range("d5:d24").Cells
    If Not IsEmpty(c) Then
        count = count + WorksheetFunction.CountA(c & "!b9:b28")
    End If
Next

I tried an alternative method to loop through second range and if the cells are not empty, then increment the variable by 1 but that's giving a Run time error 13 type mismatch error. This is what I am doing now:

    For Each c In Worksheets("Sheet1").Range("d5:d24")
    If Not IsEmpty(c) Then
        For Each c2 In Worksheets(c).Range("b9:b28")
            If Not IsEmpty(c2) Then
                'count = count + WorksheetFunction.CountA(c & "!b9:b28")
                count = count + 1
            End If
        Next
    End If
Next

Please help me out. Thanks a lot in advance to all those who take out time to reply.

2
vbaexcel
I think you need to provide .CountA with a range object and not a string. Try .CountA(Worksheets(c).Range("B9:B28")) – Peter Albert
You use cell (as range object) and try to add address notation to it. Surely that wont work. Follow @PeterAlbert suggestion. – Peter L.
Thanks for the quick replies guys. I tried @Peter Albert's solution but it threw a type mismatch error. I provided string because I thought the functions work the same in VBA as in Excel. – Amit Kumar Jha
Finally got it.Based on @PeterL.'s reply I used c.Value and it worked. Thanks a lot guys. – Amit Kumar Jha
@AmitKumarJha submit it as your own answer to avoid further efforts from community. You'll be able to accept it in a few days. – Peter L.

2 Answers

4
votes

Based on @Peter Albert and @Peter L. 's comments, finally got it working. The correct code is:

For Each c In Worksheets("Sheet1").Range("d5:d24").Cells
If Not IsEmpty(c.Value) Then
    count = count + WorksheetFunction.CountA(c.Value & "!b9:b28")
End If
Next

Thanks a lot guys :)

1
votes

Try this:

Sub CountColBForColD()
Dim c As Range
Dim r As Long   'row counter
Dim rngB As Range
Dim rngD As Range
Dim lookSheet As Worksheet

Set rngD = Sheets("Sheet1").Range("D5:D24")
Set rngB = Range("B9:B28")
r = 1

For Each c In rngD
    If Not IsEmpty(c) Then
        On Error GoTo InvalidSheetName
        Set lookSheet = Sheets(rngB(r).Value)
        On Error GoTo 0
        Count = Count + WorksheetFunction.CountA( _
            lookSheet.Range(rngB.Address))
            c.Offset(0, 1).Value = Count
            r = r + 1
    End If
NxtC:
Next

Exit Sub

InvalidSheetName:
Err.Clear
MsgBox "Sheet named in  " & rngB(r).Address & " does not exist.", vbInformation
Resume NxtC

End Sub