Counta anomaly

[stimpy]

Hi all,
I'm trying to test whether both cellls in a pair are empty, I written
a macro and a function to do the same thing both use Counta and both
return different answers:
Macro (cell2 and cell3 are values taken from cells A5 and A6)
if WorksheetFunction.CountA(cell2, cell3) = 0 Then
answer= "Blank"
Else
answer= "not blank"
End if
returns :"not blank"

The Function
if(Counta(a5,a6)=0,"Blank","not blank")
returns :"blank"

The function is returning correctly. I have tried changing the
"transition navigation keys" setting but nothing changed. The VB
debugger says cell2 and cell3 are both Empty going into the test, why
is the macro returning wrong?


I am using Excel 97 and cannot work this out.

Any help would be appreciate

Thanks

Paul

 

[Jim Rech]

The key thing here is how you set the values of cell2 and cell3, and you
didn't post that code which you should have. But based on your description,
you're passing Counta the "value" of the cells rather than the cells
themselves. The value of empty cells is 0 for many purposes (e.g., enter
=A1 in a cell and it returns 0 even though A1 is empty) and 0 is not empty.

These examples illustrate what I think is going on. In both, A1 and A2 are
empty:

Sub Bad()
Dim Cell1Val As Variant
Dim Cell2Val As Variant
Cell1Val = Range("A1").Value
Cell2Val = Range("A2").Value
MsgBox Application.CountA(Cell1Val, Cell2Val) ''2
End Sub

Sub Good()
Dim Cell1Rg As Range
Dim Cell2Rg As Range
Set Cell1Rg = Range("A1")
Set Cell2Rg = Range("A2")
MsgBox Application.CountA(Cell1Rg, Cell2Rg) ''0
End Sub