Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counta anomaly
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counta anomaly
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 -- Jim Rech Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Top N Pivot Anomaly | Excel Discussion (Misc queries) | |||
Date anomaly | Excel Discussion (Misc queries) | |||
Sort anomaly | Excel Worksheet Functions | |||
VLOOKUP Anomaly | Excel Worksheet Functions | |||
COUNTA Function not working =COUNTA(C3:C69,"NH") | Excel Worksheet Functions |