VBA: Empty and "" ?

[vuminhduc]

Please look at these lines of code:

Sub test()
x = Application.WorksheetFunction.CountIf([a1:a2], [a1])
y = Application.WorksheetFunction.CountIf([a1:a2], "")
z = [a1] = ""
End Sub

Basically there is nothing in cell a1 and a2.
Pressing F5 returns x=0, y=2 and z = True. How can it be ? Should x must be equal to y as z is True?

 

[Jeffrey20]

I think the reason is this:

Syntax
COUNTIF(range, criteria)

range    Required. One or more cells to count, including numbers or names, arrays, or references that contain numbers. [B][U]Blank and text values are ignored.[/U][/B]

criteria    Required. [U]A number, expression, cell reference,[/U] [B][U]or text string[/U][/B] that defines which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".

I think a "blank cell" is different from a cell that contains an "empty text string", at least from the point of view of the CountIf function...

So Excel detects no criteria, since it's a blank and blanks are not a usable criteria.

 

[Jeffrey20]

This is the code you where looking for:

Sub vuminhduc()

Dim cll As Range
Dim x As Long
Dim y As Long
Dim z As String

For Each cll In Range("A1:A2")
    If cll.Value = Range("A1").Value Then
    x = x + 1
    End If
    
    If cll.Value = "" Then
    y = y + 1
    End If
Next cll

If Range("A1").Value = "" Then
z = True
Else
z = False
End If

MsgBox "X: " & x & vbNewLine & "Y: " & y & vbNewLine & "Z: " & z

End Sub

 

[vuminhduc]

Thanks for replying. But so why z is true ?

 

[vuminhduc]

I think the reason is this:

Syntax
COUNTIF(range, criteria)

range    Required. One or more cells to count, including numbers or names, arrays, or references that contain numbers. [B][U]Blank and text values are ignored.[/U][/B]

criteria    Required. [U]A number, expression, cell reference,[/U] [B][U]or text string[/U][/B] that defines which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".

I think a "blank cell" is different from a cell that contains an "empty text string", at least from the point of view of the CountIf function...

So Excel detects no criteria, since it's a blank and blanks are not a usable criteria.

Thanks for replying, but so why z is true ?

 

[Jeffrey20]

Because (in z) an "empty text string" is a usable criteria; and a "blank cell" = "empty text string" (when comparing) (TRUE).

It appears that the problem is that the countif function doesn't accept (well it accepts it, but doesn't use it; I agree, this is weird, it should return an #ERROR) a "blank cell" as criteria, in that particular case a "blank cell" is different from an "empty text string" but only applies when "blank cell" is being use as a criteria.

Be aware that I'm theorizing here, I'm just intrigue by just question...


Also, you may change your first formula for this:
=IF(ISBLANK(A1),COUNTIF(A1:A2,""),COUNTIF(A1:A2,A1))
That way you'll get the correct answer.