WorksheetFunction.Sum works fine - WorksheetFunction.CountBlank does not

[CathalB]

Hi Everyone,

I am trying to get a returned count of blank cells for a selection of cells on every row of a spreadsheet and paste the total number of blanks for each row into a cell on another sheet.

The destination cells for the totals are (in bold and the order that they appear in the below code) DT, DF, DG and DH

This code already works to enter the totals in another part of the destination spreadsheet (sheet 2).
But when I have amended the working code which was exactly the same as below except that WorksheetFunction.CountBlank was WorksheetFunction.Sum.

Since changing it to WorksheetFunction.CountBlank I keep getting the error Error Message
"Unable to get the CountBlank property of the Worksheet Function Class"
when I try to run it.

I have been trying to get it working all day. I think it's related to the looping which CountBlank may not get along with?

Sub CountMissingT1COREItems()


    Dim i As Long
    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    Worksheets("Sheet2").Range("DT" & i) = WorksheetFunction.CountBlank(Range("AV" & i & ",BF" & i & ",BI" & i & ",BW" & i))
    Worksheets("Sheet2").Range("DF" & i) = WorksheetFunction.CountBlank(Range("AT" & i & ",BC" & i & ",BG" & i & ",BL" & i & ",AW" & i & ",BO" & i & ",BS" & i & ",BV" & i & ",AZ" & i & ",BJ" & i & ",BE" & i & ",BT" & i))
    Worksheets("Sheet2").Range("DG" & i) = WorksheetFunction.CountBlank(Range("AY" & i & ",BD" & i & ",BM" & i & ",BX" & i & ",AS" & i & ",AU" & i & ",BK" & i & ",BR" & i & ",BB" & i & ",BQ" & i & ",BU" & i & ",BY" & i))
    Worksheets("Sheet2").Range("DH" & i) = WorksheetFunction.CountBlank(Range("BA" & i & ",BZ" & i & ",BH" & i & ",BP" & i & ",AX" & i & ",BN" & i))
    
    Next i


End Sub

 

[Jonmo1]

Re: WorksheetFunction.Sum works fine - WorksheetFunction.CountBlank does not - HELP!

The countblank function can only take ONE range.

You can't do it by hand either..
=COUNTBLANK(A1,C1) = invalid.

 

[Jonmo1]

Re: WorksheetFunction.Sum works fine - WorksheetFunction.CountBlank does not - HELP!

However, the countA function can take multiple ranges..
Since it's the opposite of countblank, logic says we could do this

countofblanks = 4-COUNTA(A1,C1,E1,G1)

 

[CathalB]

Re: WorksheetFunction.Sum works fine - WorksheetFunction.CountBlank does not - HELP!

Hi Jonmo,

Apologies for the delayed response.
I had a sleep and another think about this and tried this (see below).
I thought if I could break up the whole thing into smaller loops it might appease the God of COUNTBLANK who says "NO!"
It worked.
Thanks for the input, it made me re-think the problem from a different perspective.

    Dim i As Long
    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    
    Worksheets("Sheet2").Range("DT" & i) = WorksheetFunction.CountBlank(Range("AV" & i)) + WorksheetFunction.CountBlank(Range("BF" & i)) + WorksheetFunction.CountBlank(Range("BI" & i)) + WorksheetFunction.CountBlank(Range("BW" & i))
    Worksheets("Sheet2").Range("DU" & i) = WorksheetFunction.CountBlank(Range("AT" & i)) + WorksheetFunction.CountBlank(Range("BC" & i)) + WorksheetFunction.CountBlank(Range("BG" & i)) + WorksheetFunction.CountBlank(Range("BL" & i)) + WorksheetFunction.CountBlank(Range("AW" & i)) + WorksheetFunction.CountBlank(Range("BO" & i)) + WorksheetFunction.CountBlank(Range("BS" & i)) + WorksheetFunction.CountBlank(Range("BV" & i)) + WorksheetFunction.CountBlank(Range("AZ" & i)) + WorksheetFunction.CountBlank(Range("BJ" & i)) + WorksheetFunction.CountBlank(Range("BE" & i)) + WorksheetFunction.CountBlank(Range("BT" & i))
    Worksheets("Sheet2").Range("DV" & i) = WorksheetFunction.CountBlank(Range("AY" & i)) + WorksheetFunction.CountBlank(Range("BD" & i)) + WorksheetFunction.CountBlank(Range("BM" & i)) + WorksheetFunction.CountBlank(Range("BX" & i)) + WorksheetFunction.CountBlank(Range("AS" & i)) + WorksheetFunction.CountBlank(Range("AU" & i)) + WorksheetFunction.CountBlank(Range("BK" & i)) + WorksheetFunction.CountBlank(Range("BR" & i)) + WorksheetFunction.CountBlank(Range("BB" & i)) + WorksheetFunction.CountBlank(Range("BQ" & i)) + WorksheetFunction.CountBlank(Range("BU" & i)) + WorksheetFunction.CountBlank(Range("BY" & i))
    Worksheets("Sheet2").Range("DW" & i) = WorksheetFunction.CountBlank(Range("BA" & i)) + WorksheetFunction.CountBlank(Range("BZ" & i)) + WorksheetFunction.CountBlank(Range("BH" & i)) + WorksheetFunction.CountBlank(Range("BP" & i)) + WorksheetFunction.CountBlank(Range("AX" & i)) + WorksheetFunction.CountBlank(Range("BN" & i))


    Next i

 

[Jonmo1]

Re: WorksheetFunction.Sum works fine - WorksheetFunction.CountBlank does not - HELP!

You're welcome.