Count non-numeric values in a worksheet range
Posted on 2014-10-12
I'm exporting some data from Access to Excel. In Access the data type of several of the columns is Text, but these columns only contain numeric values (no leading zeros).
When I export this data to Excel, it treats the data as text as well, but I would like convert it to a number. I've found that the Excel Range.TextToColumns method works to convert these values to numbers, so after exporting, I go to the various columns and use that method to convert the data.
Now I'm working on a more generic routine, and I don't want to hard code the column names, I simply want to test the data type of the field. If it is text, I want to count to see if any of the "values" in that range are non-numeric. If they are, then I won't do the conversion, but if all of the cells in a range are numeric, then I'll do the conversion.
So, is there a method or function in Excel which will allow me to get a count of the non-numeric cells in a range?