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

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

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

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

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 -

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.