 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008Counta not calculating correctly???
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  SignatureKWB 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 > Count anyone shed some light on this issue. > I am using the statement [quoted text clipped - 6 lines] > > Thanks for any help Hey Tyro, That is exactly what I was concerned with. I will check the LEN function Thanks SignatureKWB > 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 [quoted text clipped - 13 lines] > > > > Thanks for any help 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 Tyro, > That is exactly what I was concerned with. [quoted text clipped - 23 lines] >> > >> > Thanks for any help =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? > Count anyone shed some light on this issue. > I am using the statement [quoted text clipped - 8 lines] > -- > KWB SignatureDave Peterson 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. SignatureKWB > =counta() will count a cell that contains a formula that evaluate to "" as being > used. [quoted text clipped - 16 lines] > > -- > > KWB 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 [quoted text clipped - 30 lines] > > > > Dave Peterson SignatureDave Peterson Excellent information. Thanks a million for your input. Also thanks to the others who put some thought into this, Much appreciated Kelvin SignatureKWB > looping through the cells and using .clearcontents should work. > [quoted text clipped - 50 lines] > > > > > > 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 > Count anyone shed some light on this issue. > I am using the statement [quoted text clipped - 6 lines] > > Thanks for any help Henn, Seems to work if I loop through and clear the contents of empty cells. Thanks for the input. Kelvin SignatureKWB > Is that possible that some of those cells contain "" (empty string) in some > reason? [quoted text clipped - 14 lines] > > > > Thanks for any help
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.