CODEWITHSUNDEEP
vbaexcel
Amit Kumar Jha
Amit Kumar Jha

Reputation: 599

WorksheetFunction.CountA not returning correct value

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.

Upvotes: 1

Views: 10163

Answers (2)

David Zemens
David Zemens

Reputation: 53623

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

Upvotes: 1

Amit Kumar Jha
Amit Kumar Jha

Reputation: 599

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 :)

Upvotes: 4

Related Questions