Count cells in a Column problem

    I have been working at this for about 2 hours now and have tried to modify code from other threads with no joy.



    I am trying to count the number of cells that contain a number (any number or numbers). The most that would ever be found with a number in the column would be 350 (D2:D351).
    The column I am wanting to count is Labeled "Store Numbers" and is column D. I want to take this count and store it as a variable so that I can use it in some other code I already have put together.


    I have attached a stripped down copy of the file I am working with if this helps.



    Thanks in advance

    isnumeric


    Have you tried using the IsNumeric() function?


    Here's a code snippet:



    Thanks Jong,


    Well I got through it with no errors this time, but the counter returned 329 and there is only 136 cells with numbers in them this time. The cells are formatted as General when I recieve the Workbook. I just changed the format of the cells to Numbers and still no luck.


    Any ideas?

    The cells are empty. That is why I am getting dizzy working on this.



    The complete file is 170K in file size. Not sure if that would help or not.



    Thanks

    Weird... Try doing the following (modified from my above code)


    Just about there.


    Okay, that brought the count to 114. I still have 136 occupied cells in the range. I did some quick math and it appears that the code is not counting cells that have a comma in it. This is probably very clear and understandable to most of you.
    Is the IsNumeric() function not counting those cells because of the comma.


    Here is what my cells look like:
    some will just have 3011 in them and some could be like 3011,3258,4298 and so on.


    Jon, I really appreciate you helping me with this.

    Yes, the IsNumeric Function will only be TRUE if the entire cell is a number.


    If there is a comma in the cell, it will be False.



    In your case, you cannot use the IsNumeric function, due to the fact that your cells contain commas. Now, if you know for a fact that the cell will either be empty, or contain numbers , then you can just check for a blank. If the cell is blank, don't count it. If the cell contains data, count it.


    Code
    Dim Counter As Integer 
Counter = 0 
 
For Each cell In Worksheets("MySheet").Range("D2:D351") 
    If cell.value <> "" Then 
        Counter = Counter + 1 
    End If 
Next cell 
 
MsgBox "There are a total of " & Counter & " Numbers in range D2:D351"

    Jon,


    Thank you very much. I am sorry I wasn't more clear earlier.
    I normally include sample data with my posts, but since I included the file attachment I didn't do that this time. I now realize I should do both.


    I hope I didn't waste too much of your time.


    Thanks again and have a good weekend. :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login