Counting blank cells with formulas in them

[austenr]

How can you count the number of cells that are blank but contain formulas. You cannot check for spaces because it is not = to spaces. You cannot check > 0 (My data is a number if not blank) or at least I cannot get it to work.

Here is my code


Sub countblanks()
Dim c As Long
Dim myrange As Variant
For Each myrange In Range("C2:C11358")
If myrange > 3700000 Then
c = c + 1
End If
Next myrange
MsgBox ("Non Matches " & c)
End Sub

Help would be appreciated. Thanks :dunno

[Killian]

I would test for no text ("") and use the HasFormula property

If Range("myRange").HasFormula And Range("myRange").Text = "" Then

[mdmackillop]

Hi Austen
For large number of cells, try finding with special cells first (Edit/GoTo/Special), then looping through found cells, or maybe a second find operation.


Selection.SpecialCells(xlCellTypeFormulas, 23).Select
For Each cell In Selection
If cell.Text = "" Or cell.Value = 0 Then
c = c + 1
End If
Next
MsgBox c

[Zack Barresse]

MD, you could merge your first 2 lines ...


For Each cell In Selection.SpecialCells(xlCellTypeFormulas, 23)

Although, I'd probably just use Len ...


Sub foofoo()
Dim cell As Range
For Each cell In Selection.SpecialCells(xlCellTypeFormulas, 23)
If Len(cell.Value) = 0 Then
c = c + 1
End If
Next
MsgBox c
End Sub

We may be able to make this faster by using AutoFilter - if the range is large enough..

[mdmackillop]

MD, you could merge your first 2 lines ...
:whip

[Cyberdude]

firefyter, can it be said that

If Len(cell.Value) = 0

is equivalent to


If cell.value = ""

In the past I've used a lot of the second form, but I've been uneasy about it.

[Zack Barresse]

Yes, they both compute the same thing really. Although it can be argued as one is faster than the other, either one should work just fine. One may use one over the other depending on individual circumstances and personal preference.

[mdmackillop]

If the formula in A3 is =A1+A2, then Len([A3].Value) = 1, even if there is nothing in either A1 or A2, or am I missing something?

[Bob Phillips]

If the formula in A3 is =A1+A2, then Len([A3].Value) = 1, even if there is nothing in either A1 or A2, or am I missing something?

That is because the + coerces it to a number, so the cell will contain 0.

[Zack Barresse]

Yes, this is why it may not always be a case for Len()=0, or even vbNullString as the case may be. I always try, when creating a spreadsheet/formula structure, to ensure all returns equivelate to some real means, and always know what my null value should be. Such as if I am dealing with Currency, I probably want the 0, and format either as Currency or Accounting ($ - ). But the task should always reflect the data structure.