Counting blank cells with formulas in them

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

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

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

Code:

---

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

---

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.

Code:

---

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

---

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

Code:

---

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

---

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

Code:

---

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..

Quote:

---

Originally Posted by firefytr

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

---

:whip

firefyter, can it be said that

Code:

---

If Len(cell.Value) = 0

---

is equivalent to

Code:

---

If cell.value = ""

---

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

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.

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?

Quote:

---

Originally Posted by 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?

---

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

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.