Count number of values in a column (Values are Number in Text format)

Question

I've run into a snag.

I'm writing a macro that pulls values from our mainframe. The number of values could change with each run. The values are Numbers in a Text format. It is important to keep this format because they could be used for another purpose. Unfortunately, since I work with sensitive data I am unable to post the code I have because it is on a work laptop.

Basically, the code spins through a table of values and pulls each value in a certain position. At the end of my code I'm wanting to put a COUNT to count the number of cells with a value. Remember this value could change, For example in my 1st run I can pull back 100 values, 2nd run could pull back 1000 values.

At the end of my loop I thought about putting the following, but since the values on in Text format I get an error.

Application.WorksheetFunction.Count Range("D1:D7000")
Me.Cells(x,x) = Application.WorksheetFunction.Count Range("D1:D7000")

Error Message is

Run-Time Error 1004

Application-defined or object defined error

I know it has to do with the format, because when I change the values to text it works fine.

Ideas?

Answer 1

CountA might be what you need. It counts non-empty cells whereas Count counts cells containing numbers