I'm writing a program that needs to convert numeric values in a Excel file to display as numbers and not text. The below code works, however is slow (very slow for large files) because it must check every single cell to see if it is a number, and than convert it to a number format.
Is there a way to convert an entire column to numbers, as if you were to select the entire column values in Excel, and hit "Convert to Numbers". I'm assuming there is a way to code it, however recording a macro and viewing the VBA does not show the conversion code, just the select code.
Essentially, I want this in code:
1. Check to see if cell A2, B2, C2, etc are numbers.
2. If they are numbers, select the entire column and convert from text to numbers.
3. If not, ignore and check next column.
Please note: Selecting a column with mixed numeric and non-numeric values in Excel and selecting "Convert to Numbers" only converts the numeric values. Using CDec without checking to see if it is numeric first will result in an error. (This is pretty obvious as it is trying to convert a string to a decimal, but even if I selected the whole column programically, how could I avoid that error? Is there a different code I should be using rather than CDec?) Thank's for any and all assistance.
VB.NET:
raXL = shXL.Range("A2", ColumnChar & intLastRow) ' ColumnChar is last column letter, intLastRow is the last row number.
For Each xCell In raXL
If IsNumeric(xCell.Value) = True Then
xCell.Value = CDec(xCell.Value)
End If
Next xCellIs there a way to convert an entire column to numbers, as if you were to select the entire column values in Excel, and hit "Convert to Numbers". I'm assuming there is a way to code it, however recording a macro and viewing the VBA does not show the conversion code, just the select code.
Essentially, I want this in code:
1. Check to see if cell A2, B2, C2, etc are numbers.
2. If they are numbers, select the entire column and convert from text to numbers.
3. If not, ignore and check next column.
Please note: Selecting a column with mixed numeric and non-numeric values in Excel and selecting "Convert to Numbers" only converts the numeric values. Using CDec without checking to see if it is numeric first will result in an error. (This is pretty obvious as it is trying to convert a string to a decimal, but even if I selected the whole column programically, how could I avoid that error? Is there a different code I should be using rather than CDec?) Thank's for any and all assistance.
