Re: Counta not calculating correctly??? by Kelvin
Kelvin
Mon Mar 10 19:45:02 CDT 2008
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
>