Prev: Pivot table has source data has exceeded 65536 records typemisma
Next: FormulaR1C1 using variables
From: JoeBoynton on 21 Apr 2010 12:15
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.
From: Peter T on 21 Apr 2010 13:00
Record a macro to macro to give you the basic syntax in VBA. A different
way, put 1 in a cell, copy, select the range, pastespecial, multiply. Again
record a macro.

Regards,
Peter T

"JoeBoynton" <JoeBoynton(a)discussions.microsoft.com> wrote in message
news:E5069FEB-100C-470C-AD5D-6BBB1B2CE9AD(a)microsoft.com...
> 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.


From: RyGuy on 21 Apr 2010 13:35
Hummm, I'm trying to figure out what you've got there. Try this code:

Sub Remove_CR_LF()

With Selection
..Replace What:=Chr(39), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(44), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With

End Sub

You just have to know the ASCII Character...I think....


"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.
From: Neal Zimm on 21 Apr 2010 14:35
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.
From: Neal Zimm on 21 Apr 2010 14:47
Sorry, forgot a most important item.

In the TextToNum proc,

put Application.ScreenUpdating = False
before this line of code

For Each Area In ConvertRng


put Application.ScreenUpdating = True

before the End Sub statement.

For thousands of cells, that should speed it up considerably.
--
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.
 |  Next  |  Last
Pages: 1 2 3
Prev: Pivot table has source data has exceeded 65536 records typemisma
Next: FormulaR1C1 using variables