Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
inability to apply formulas
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. -- Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
inability to apply formulas
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] On Fri, 29 Jan 2010 06:19:01 -0800, Paul Roche wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
inability to apply formulas
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. -- Gary''s Student - gsnu201001 "Paul Roche" wrote: 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. -- Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I insert a row & have the existing formulas apply to new ce | Excel Discussion (Misc queries) | |||
How can I insert a row and have the existing formulas apply? | Excel Discussion (Misc queries) | |||
How can I insert a row and have the existing formulas apply? | Excel Discussion (Misc queries) | |||
How do you apply Names in formulas? | Excel Discussion (Misc queries) | |||
Using an Activex DLL to apply formulas in Excel | Excel Programming |