Counta not calculating correctly???

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
 
T

Tyro

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
 
K

Kelvin

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

Thanks
 
D

Dave Peterson

=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?
 
H

Henn Sarv

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
 
T

Tyro

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

Tyro
 
K

Kelvin

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.
 
D

Dave Peterson

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...
 
K

Kelvin

Excellent information.

Thanks a million for your input.

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

Kelvin
 
K

Kelvin

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