These two small procs should do the trick.
You can:
1a. Use a macro of your own to Set ConvertRng to the Range of cells you
want.
b. Call TextToNum(ConvertRng)

or

2a. Manually select the range of cells you want to convert.
b. Get to macros, Run ConvertText


Sub ConvertText()
Dim ConvertRng As Range
'Put your code to Set ConvertRng here
'or, select cells in the sheet and run this
'macro
Call TextToNum(ConvertRng)
End Sub


Sub TextToNum(Optional ConvertRng As Range = Nothing)
Dim Value
Dim Area As Range, OneCell As Range

'If you don't supply a range, the cells to be converted
'will be the ones you select prior to running this macro.
If ConvertRng Is Nothing Then Set ConvertRng = Selection

For Each Area In ConvertRng
For Each OneCell In Area
With OneCell
If IsNumeric(.Value) Then
Value = .Value
.NumberFormat = "general" 'or whatever numeric format you want
.Value = Value
End If
End With
Next OneCell
Next Area
End Sub
--
Neal Z


"JoeBoynton" wrote:

Hi,
I need help on how to automate the conversion of a range of Excel cells from
text to numbers. I'm using Excel 2007 to test, VB.NET 2005 and Excel 9.0
Object lib. I've read some rows of data into an array and I set my Excel
Range = to the array which copies everything in quickly which is what I want.
Cell by cell is very slow with a hi number of records. Everything is fine,
except the cells with numbers were copied as text and those cells have
warning flags in the corner with the first 2 options in the dropdown being:
"Number Stored As Text" and Convert To Number". Clicking on Convert to
Number, or even clicking inside the cell a couple of times and then outside
fixes the problem and you can tell cause the warning is gone and numeric
formatting is applied. How can I automate this ConvertToNumber in code. If
I create thousands of these cells, the user can't be expected to convert each
one manually and I need to automate it. All my searches have netted zero,
zilcho, nada. Is there some hidden method somewhere that allows me to
convert a range or even loop thru the range cell by cell and convert the
value of each of these cells to Number instead of Text? Your help would be
appreciated.