Counta not calculating correctly???

  • Thread starter Thread starter Kelvin
  • Start date Start date
K

Kelvin

Count anyone shed some light on this issue.
I am using the statement
n = WorksheetFunction.CountA(Cells(i, 13), Cells(i, 17), Cells(i, 22))

I am trying to get a value for how many of the 3 cells have data in them.
While watching my n count I am seeing it reading 2 when there is clearly
only data in one of the cells.
Any idea why this would be reading incorrectly?

Thanks for any help
 
COUNTA works fine. Are you sure the cells are empty? Perhaps one of them has
a space or other non-visible character in it. Those will be counted by
COUNTA
Try using the LEN function. If the cells are truly empty, LEN will return 0

Tyro
 
Hey Tyro,
That is exactly what I was concerned with.
I will check the LEN function

Thanks
 
=counta() will count a cell that contains a formula that evaluate to "" as being
used.

And if you convert that cell that evaluates to "", excel still will count it
using =counta().

Any chance that's what happened?
 
Is that possible that some of those cells contain "" (empty string) in some
reason?

What if You try to manually add some Function on sheet somewhere countA-ing
of those same cells?

Henn
 
Dave's input is correct. A formula that returns "", the empty string will
have a LEN of 0 but be counted by COUNTA

Tyro
 
Hey Dave,
Thanks for the input
I wrote a macro to "clear contents" of any empty cells.
Will that truely clear the contents to where CountA will work?

I was also stepping through while watching some variables. len(s) would be
0,0,4 and my counta variable would show 2.
 
looping through the cells and using .clearcontents should work.

If I've converted formulas to values, I like to select the range:
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

If the number of cells to inspect is a lot, then this should work more quickly.

=====
And yep, checking the len(.value) = 0 (or len(trim(.value))) should work ok,
too.
Hey Dave,
Thanks for the input
I wrote a macro to "clear contents" of any empty cells.
Will that truely clear the contents to where CountA will work?

I was also stepping through while watching some variables. len(s) would be
0,0,4 and my counta variable would show 2.
Click to expand...
 
Excellent information.

Thanks a million for your input.

Also thanks to the others who put some thought into this,
Much appreciated

Kelvin
 
Henn,
Seems to work if I loop through and clear the contents of empty cells.
Thanks for the input.
Kelvin
 
Back
Top