inability to apply formulas

[Paul Roche]

I am need of some assistance. Please let me know if you can help me re-format
some data.
I copied data from a list and saved it into an excel spreadsheet. When I
attempt to apply a formula to this data I get VALUE errors.
I can re-key the numbers into the same cells and then the formula will work??
How can I convert, or re-format this data (it is all numbers) into a
recognizable format? I have over 13,200 cells to work with.
Thanks for your assistance in addvancxe.

 

[Chip Pearson]

Without knowing the type of data you pasted and the formulas you are
attempting to apply to that data, it is difficult to diagnose the
problem. Most likely, the data got imported as Text format, rather
than being converted to numeric data. That is, you might have a cell
containing the Text value "1" rather than a numeric value 1. If you
attempt to use the text value in a calculation, you'll usually get a
#VALUE error.

The following code will convert the selected cells to General format,
and then sets the cells to their own value, which will force a
conversion from Text to number is required by the value.

Select the cells in question and run the following code:

Sub ConvertToVals()
' assumes (1) Selection contains no formulas,
' (2) Selection contains no arrays
' (3) Selection contains no merged cells.
Selection.NumberFormat = "General"
Selection.Value = Selection.Value
End Sub

It is assumed that the selection does not have formulas, arrays, or
merged cells.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Gary''s Student]

You can try to convert them if they are Text as follows:

Sub fixum()
For Each r In Selection
v = r.Value
r.Clear
r.NumberFormat = "General"
r.Value = v
Next
End Sub

First save your work. Then select some cells. Then run the macro.

 

[Don Guillett]

In addition, here is one I have in my personal.xls assigned to a custom
button

Sub fixmynums()
Application.ScreenUpdating = False
On Error Resume Next
For Each c In Selection
If Trim(Len(c)) > 0 And c.HasFormula = False Then
c.NumberFormat = "General"
c.Value = CDbl(c)
End If
Next

Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Without knowing the type of data you pasted and the formulas you are
attempting to apply to that data, it is difficult to diagnose the
problem. Most likely, the data got imported as Text format, rather
than being converted to numeric data. That is, you might have a cell
containing the Text value "1" rather than a numeric value 1. If you
attempt to use the text value in a calculation, you'll usually get a
#VALUE error.

The following code will convert the selected cells to General format,
and then sets the cells to their own value, which will force a
conversion from Text to number is required by the value.

Select the cells in question and run the following code:

Sub ConvertToVals()
' assumes (1) Selection contains no formulas,
' (2) Selection contains no arrays
' (3) Selection contains no merged cells.
Selection.NumberFormat = "General"
Selection.Value = Selection.Value
End Sub

It is assumed that the selection does not have formulas, arrays, or
merged cells.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

I am need of some assistance. Please let me know if you can help me
re-format
some data.
I copied data from a list and saved it into an excel spreadsheet. When I
attempt to apply a formula to this data I get VALUE errors.
I can re-key the numbers into the same cells and then the formula will
work??
How can I convert, or re-format this data (it is all numbers) into a
recognizable format? I have over 13,200 cells to work with.
Thanks for your assistance in addvancxe.