PDA

View Full Version : [SOLVED] Counting blank cells with formulas in them



austenr
07-18-2005, 11:49 AM
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
07-18-2005, 12:11 PM
I would test for no text ("") and use the HasFormula property

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

mdmackillop
07-18-2005, 01:33 PM
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
07-18-2005, 01:40 PM
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
07-18-2005, 01:51 PM
MD, you could merge your first 2 lines ...
:whip

Cyberdude
07-18-2005, 03:01 PM
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
07-18-2005, 03:09 PM
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
07-18-2005, 03:34 PM
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
07-18-2005, 03:45 PM
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
07-18-2005, 05:05 PM
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.