Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Row of text to values
Hello,
I am trying to convert a column of #'s formatted as text ( GENERAL ) to values for use in calculation/lookup functions. I would like to plug solution into existing macro that performs these mentioned functions. The column is 'AB' and I need no 'nice messages or if variable if statements', Only for entire column to be converted into a # format. The #'d cells have the green "ERROR" indicator in top left corner of cell and I get desired results by: 1) clicking cell 2) clicking "ERROR" indicator popup 3) choosing "Convert to Number' but it does not record these actions with the VBA recorder This is Driving me Nuts, can someone help with simple solution? Brgds, SteveT |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Row of text to values
On Nov 14, 12:14 pm, SteveT wrote:
Hello, I am trying to convert a column of #'s formatted as text ( GENERAL ) to values for use in calculation/lookup functions. I would like to plug solution into existing macro that performs these mentioned functions. The column is 'AB' and I need no 'nice messages or if variable if statements', Only for entire column to be converted into a # format. The #'d cells have the green "ERROR" indicator in top left corner of cell and I get desired results by: 1) clicking cell 2) clicking "ERROR" indicator popup 3) choosing "Convert to Number' but it does not record these actions with the VBA recorder This is Driving me Nuts, can someone help with simple solution? Brgds, SteveT Did you try to copy the whole Column, and Paste special as values in the same column? Copy AB -- Edit = Paste Special... = Select "Values" and click OK hth Carlo |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Row of text to values
I Found It :)
Simple solution found through MS Direct sight. For those interested its below: Columns("AB:AB").Select For Each xCell In Selection xCell.Value = xCell.Value Next xCell "SteveT" wrote: Hello, I am trying to convert a column of #'s formatted as text ( GENERAL ) to values for use in calculation/lookup functions. I would like to plug solution into existing macro that performs these mentioned functions. The column is 'AB' and I need no 'nice messages or if variable if statements', Only for entire column to be converted into a # format. The #'d cells have the green "ERROR" indicator in top left corner of cell and I get desired results by: 1) clicking cell 2) clicking "ERROR" indicator popup 3) choosing "Convert to Number' but it does not record these actions with the VBA recorder This is Driving me Nuts, can someone help with simple solution? Brgds, SteveT |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Row of text to values
You might want to refine that a bit so's it doesn't operate on the entire
column. Sub change_to_nums() Dim rng As Range Dim xCell as Range Set rng = Range(Range("AB1"), Range("AB" & Rows.Count). _ End(xlUp).Address) For Each xCell In rng xCell.Value = xCell.Value Next xCell End Sub Gord Dibben MS Excel MVP On Wed, 14 Nov 2007 09:34:01 -0800, SteveT wrote: I Found It :) Simple solution found through MS Direct sight. For those interested its below: Columns("AB:AB").Select For Each xCell In Selection xCell.Value = xCell.Value Next xCell "SteveT" wrote: Hello, I am trying to convert a column of #'s formatted as text ( GENERAL ) to values for use in calculation/lookup functions. I would like to plug solution into existing macro that performs these mentioned functions. The column is 'AB' and I need no 'nice messages or if variable if statements', Only for entire column to be converted into a # format. The #'d cells have the green "ERROR" indicator in top left corner of cell and I get desired results by: 1) clicking cell 2) clicking "ERROR" indicator popup 3) choosing "Convert to Number' but it does not record these actions with the VBA recorder This is Driving me Nuts, can someone help with simple solution? Brgds, SteveT |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Row of text to values
Type in 1 in any free cell. Copy this cell. Highlight your data that
you want converted to number. Go to Edit-Paste Special; select Values and Multiply. On Nov 14, 3:45 pm, Gord Dibben <gorddibbATshawDOTca wrote: You might want to refine that a bit so's it doesn't operate on the entire column. Sub change_to_nums() Dim rng As Range Dim xCell as Range Set rng = Range(Range("AB1"), Range("AB" & Rows.Count). _ End(xlUp).Address) For Each xCell In rng xCell.Value = xCell.Value Next xCell End Sub Gord Dibben MS Excel MVP On Wed, 14 Nov 2007 09:34:01 -0800, SteveT wrote: I Found It :) Simple solution found through MS Direct sight. For those interested its below: Columns("AB:AB").Select For Each xCell In Selection xCell.Value = xCell.Value Next xCell "SteveT" wrote: Hello, I am trying to convert a column of #'s formatted as text ( GENERAL ) to values for use in calculation/lookup functions. I would like to plug solution into existing macro that performs these mentioned functions. The column is 'AB' and I need no 'nice messages or if variable if statements', Only for entire column to be converted into a # format. The #'d cells have the green "ERROR" indicator in top left corner of cell and I get desired results by: 1) clicking cell 2) clicking "ERROR" indicator popup 3) choosing "Convert to Number' but it does not record these actions with the VBA recorder This is Driving me Nuts, can someone help with simple solution? Brgds, SteveT- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Row of text to values
Please note OP's request.......
I would like to plug solution into existing macro that performs these mentioned functions. Gord On Wed, 14 Nov 2007 21:54:08 -0000, iliace wrote: Type in 1 in any free cell. Copy this cell. Highlight your data that you want converted to number. Go to Edit-Paste Special; select Values and Multiply. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting does not change when values change. ?? | Excel Worksheet Functions | |||
Change text colour if range of values all zero | Excel Worksheet Functions | |||
How can I change text to proper text in multiple cells. | Excel Discussion (Misc queries) | |||
Change of text or background color doesn't change on the screen. | Excel Discussion (Misc queries) | |||
HOW TO CHANGE BAHTTEXT (THAI TEXT) TO ENGLISH TEXT IN EXCEL | Excel Worksheet Functions |