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
--
KWB

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

"Kelvin" <(E-Mail Removed)> wrote in message
news:8D315915-BC02-4BF2-B97A-(E-Mail Removed)...
> 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
> --
> KWB

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

Thanks
--
KWB


"Tyro" wrote:

> 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
>
> "Kelvin" <(E-Mail Removed)> wrote in message
> news:8D315915-BC02-4BF2-B97A-(E-Mail Removed)...
> > 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
> > --
> > KWB
>
>
>

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

Kelvin wrote:
>
> 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
> --
> KWB

--

Dave Peterson

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

"Kelvin" <(E-Mail Removed)> kirjutas sõnumis
news:8D315915-BC02-4BF2-B97A-(E-Mail Removed)...
> 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
> --
> KWB

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

Tyro

"Kelvin" <(E-Mail Removed)> wrote in message
news:FC5818EB-EBF3-4787-9ED9-(E-Mail Removed)...
> Hey Tyro,
> That is exactly what I was concerned with.
> I will check the LEN function
>
> Thanks
> --
> KWB
>
>
> "Tyro" wrote:
>
>> 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
>>
>> "Kelvin" <(E-Mail Removed)> wrote in message
>> news:8D315915-BC02-4BF2-B97A-(E-Mail Removed)...
>> > 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
>> > --
>> > KWB
>>
>>
>>

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


"Dave Peterson" wrote:

> =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?
>
> Kelvin wrote:
> >
> > 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
> > --
> > KWB
>
> --
>
> 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.

Kelvin wrote:
>
> 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.
> --
> KWB
>
> "Dave Peterson" wrote:
>
> > =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?
> >
> > Kelvin wrote:
> > >
> > > 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
> > > --
> > > KWB
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Excellent information.

Thanks a million for your input.

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

Kelvin
--
KWB


"Dave Peterson" wrote:

> 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.
>
> Kelvin wrote:
> >
> > 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.
> > --
> > KWB
> >
> > "Dave Peterson" wrote:
> >
> > > =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?
> > >
> > > Kelvin wrote:
> > > >
> > > > 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
> > > > --
> > > > KWB
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

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


"Henn Sarv" wrote:

> 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
>
> "Kelvin" <(E-Mail Removed)> kirjutas sõnumis
> news:8D315915-BC02-4BF2-B97A-(E-Mail Removed)...
> > 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
> > --
> > KWB
>
>