Prev: How do you turn off the clip board option?
Next: Format Text to Number
From: Bruce D. on 4 May 2010 12:00
I am using excel 2007 and I am creating a macro that will count the number of
account numbers in a column. The problem is that the account numbers are
stored as text not number. This prevents the count() function from working.
Is there a way to get this accomplished.

Thanks!
--
Bruce
From: Bob Phillips on 4 May 2010 12:04
Use COUNTA, that counts a on-empty cell.

--

HTH

Bob

"Bruce D." <BruceD(a)discussions.microsoft.com> wrote in message
news:9F4CD469-837A-40DB-BF89-AF0830B75CA4(a)microsoft.com...
>I am using excel 2007 and I am creating a macro that will count the number
>of
> account numbers in a column. The problem is that the account numbers are
> stored as text not number. This prevents the count() function from
> working.
> Is there a way to get this accomplished.
>
> Thanks!
> --
> Bruce


From: Tom Hutchins on 4 May 2010 12:31
Try COUNTA instead of COUNT. COUNTA counts non-blank cells, not just numbers.
To use COUNTA in a macro you will have to refer to it like this:
Application.WorksheetFunction.CountA(etc.)
since it is a worksheet function, not a VBA function.

Hope this helps,

Hutch

"Bruce D." wrote:

> I am using excel 2007 and I am creating a macro that will count the number of
> account numbers in a column. The problem is that the account numbers are
> stored as text not number. This prevents the count() function from working.
> Is there a way to get this accomplished.
>
> Thanks!
> --
> Bruce
From: Paul C on 4 May 2010 12:36
You can use the VALUE function to convert text to numbers, but you don;t even
need to do this to get a count of unique values

This post has a very simple formula that I have used before

http://www.microsoft.com/office/community/en-us/default.mspx?query=count+unique+values+in+a+range&dg=&cat=en-us-excel&lang=en&cr=US&pt=f3f7ac8a-4ea0-4c36-bed9-8feae6d75298&catlist=&dglist=&ptlist=&exp=&sloc=en-us
--
If this helps, please remember to click yes.


"Bruce D." wrote:

> I am using excel 2007 and I am trying to create a macro that will convert
> account numbers which are stored as text to a number format. I want to get a
> record count on how many account numbers there are. Any ideas?
>
> Thanks,
> --
> Bruce
From: Bruce D. on 4 May 2010 12:37
Yes, that works. Thanks!
--
Bruce


"Tom Hutchins" wrote:

> Try COUNTA instead of COUNT. COUNTA counts non-blank cells, not just numbers.
> To use COUNTA in a macro you will have to refer to it like this:
> Application.WorksheetFunction.CountA(etc.)
> since it is a worksheet function, not a VBA function.
>
> Hope this helps,
>
> Hutch
>
> "Bruce D." wrote:
>
> > I am using excel 2007 and I am creating a macro that will count the number of
> > account numbers in a column. The problem is that the account numbers are
> > stored as text not number. This prevents the count() function from working.
> > Is there a way to get this accomplished.
> >
> > Thanks!
> > --
> > Bruce
 |  Next  |  Last
Pages: 1 2
Prev: How do you turn off the clip board option?
Next: Format Text to Number