VALUE Error
 

I have a large spreedsheet from L2 to BE 1268
The majoority of the cells contain data. It appears that not all the data is
NUMERIC as I am getting VALUE errors. I would like a macro that would check
all the cells to see which one are NOT numeric so that I could change them to
MUNERIC and avoid all the VALUE errors I am getting
Going accross from l to be I need 4 different totals( I total col L,M,N,s,t u
an other total might total the following col O,P,Q,R etc etc
Thanks

VALUE Error
 

"pcor" wrote:
I would like a macro that would check all the cells to see
which one are NOT numeric so that I could change them
to MUNERIC

Well, the macro below might do what you want. It even makes the necessary
changes, subject to a yes/no prompt. You can eliminate the prompt if it
becomes tedious and you trust the changes.

However, I question whether that it is the right solution for your problem.

In fact, it might not solve your problem at all. No way to tell, since you
did not fully explain the problem.


It appears that not all the data is NUMERIC as I am getting
VALUE errors.
[....]
Going accross from l to be I need 4 different totals( I total col
L,M,N,s,t u an other total might total the following col O,P,Q,R
etc etc

It would help us help you if you showed us the formulas that are causing
#VALUE errors.

Also show us the contents of some of the non-numeric cells that cause the
#VALUE errors. For example, pick one formula that returns #VALUE, and use
Evaluate Formula (Tools Formula Auditing in Excel 2003) to identify one or
two cells that cause the error. (You might need to correct the error in the
first cell in order to find a second cell.)

For example, if the root cause of some/most/all #VALUE errors is references
to cells with null strings (""), the macro below will not solve the problem.
The macro simply does what you said you would do, namely: "change them
numeric"; but that is only if it is possible.

The real solution might be some appropriate change(s) to the formulas that
are resulting in #VALUE errors.


Macro.... FYI, Excel is somewhat fickle about what it treats as text v.
number. The macro was developed empirically to work around some of that
fickleness.


Sub doit()
Dim cell As Range, fml As String, x
For Each cell In Range("L2:BE1268") 'CHANGE AS NEEDED
'create formula: AND(ISTEXT(cell), NOT(ISERROR(--cell)))
fml = "and(IsText(" & cell.Address & "),not(isError(--" & _
cell.Address & ")))"
If Evaluate(fml) Then
'move "cursor" to cell referred to in the Msgbox prompt
cell.Select
'default to "no change"
x = MsgBox("Change " & cell.Address & "?", _
vbYesNo + vbDefaultButton2)
If x = vbYes Then
'change numeric text to number
cell = --cell
'change Text format to General; else preserve numeric format
If cell.NumberFormat = "@" Then cell.NumberFormat = "general"
cell.Columns.AutoFit 'avoid #### result
End If
End If
Next cell
End Sub


----- original message -----

"pcor" wrote in message
...
I have a large spreedsheet from L2 to BE 1268
The majoority of the cells contain data. It appears that not all the data
is
NUMERIC as I am getting VALUE errors. I would like a macro that would
check
all the cells to see which one are NOT numeric so that I could change them
to
MUNERIC and avoid all the VALUE errors I am getting
Going accross from l to be I need 4 different totals( I total col
L,M,N,s,t u
an other total might total the following col O,P,Q,R etc etc
Thanks

VALUE Error
 

pcor wrote:
I have a large spreedsheet from L2 to BE 1268
The majoority of the cells contain data. It appears that not all the data is
NUMERIC as I am getting VALUE errors. I would like a macro that would check
all the cells to see which one are NOT numeric so that I could change them to
MUNERIC and avoid all the VALUE errors I am getting
Going accross from l to be I need 4 different totals( I total col L,M,N,s,t u
an other total might total the following col O,P,Q,R etc etc
Thanks

If your problem really is text masquerading as numbers you can probably
fix it without inspecting the cells.

First, ensure the cells are actually formatted as numbers. Just select
the range and set a numeric format.

Of course, this by itself will not fix the problem because it does not
change the way existing values are stored.

The second step is to place the number 1 in a cell somewhere, anywhere.
Select the cell, copy, then select your numeric data range, Edit | Paste
Special | and tick the options for Values and Multiply | OK.

Hope this helps!

VALUE Error
 

Note....

I wrote:
You can eliminate the prompt if it
becomes tedious and you trust the changes.

I should have cautioned you to copy the Excel file before using this macro,
since any changes are irreversible.

Moreover, an example of a change the macro might make that you might not
want is: a numeric string that is not intended to be used as a number,
notably credit card numbers, product serial numbers etc that might exceed 15
digits.


----- original message -----

"JoeU2004" wrote in message
...
"pcor" wrote:
I would like a macro that would check all the cells to see
which one are NOT numeric so that I could change them
to MUNERIC

Well, the macro below might do what you want. It even makes the necessary
changes, subject to a yes/no prompt. You can eliminate the prompt if it
becomes tedious and you trust the changes.

However, I question whether that it is the right solution for your
problem.

In fact, it might not solve your problem at all. No way to tell, since
you
did not fully explain the problem.


It appears that not all the data is NUMERIC as I am getting
VALUE errors.
[....]
Going accross from l to be I need 4 different totals( I total col
L,M,N,s,t u an other total might total the following col O,P,Q,R
etc etc

It would help us help you if you showed us the formulas that are causing
#VALUE errors.

Also show us the contents of some of the non-numeric cells that cause the
#VALUE errors. For example, pick one formula that returns #VALUE, and use
Evaluate Formula (Tools Formula Auditing in Excel 2003) to identify one
or
two cells that cause the error. (You might need to correct the error in
the
first cell in order to find a second cell.)

For example, if the root cause of some/most/all #VALUE errors is
references
to cells with null strings (""), the macro below will not solve the
problem.
The macro simply does what you said you would do, namely: "change them
numeric"; but that is only if it is possible.

The real solution might be some appropriate change(s) to the formulas that
are resulting in #VALUE errors.


Macro.... FYI, Excel is somewhat fickle about what it treats as text v.
number. The macro was developed empirically to work around some of that
fickleness.


Sub doit()
Dim cell As Range, fml As String, x
For Each cell In Range("L2:BE1268") 'CHANGE AS NEEDED
'create formula: AND(ISTEXT(cell), NOT(ISERROR(--cell)))
fml = "and(IsText(" & cell.Address & "),not(isError(--" & _
cell.Address & ")))"
If Evaluate(fml) Then
'move "cursor" to cell referred to in the Msgbox prompt
cell.Select
'default to "no change"
x = MsgBox("Change " & cell.Address & "?", _
vbYesNo + vbDefaultButton2)
If x = vbYes Then
'change numeric text to number
cell = --cell
'change Text format to General; else preserve numeric format
If cell.NumberFormat = "@" Then cell.NumberFormat = "general"
cell.Columns.AutoFit 'avoid #### result
End If
End If
Next cell
End Sub


----- original message -----

"pcor" wrote in message
...
I have a large spreedsheet from L2 to BE 1268
The majoority of the cells contain data. It appears that not all the data
is
NUMERIC as I am getting VALUE errors. I would like a macro that would
check
all the cells to see which one are NOT numeric so that I could change
them
to
MUNERIC and avoid all the VALUE errors I am getting
Going accross from l to be I need 4 different totals( I total col
L,M,N,s,t u
an other total might total the following col O,P,Q,R etc etc
Thanks

VALUE Error
 

Hi,

1. Select the entire range and press F5, Special, Constants, and uncheck
Numbers and click OK. If all cells are numeric you will get a message
otherwise they will all be selected. You can press Tab or Shift Tab to move
from one to the next without breaking the highlighting.
You can also press Enter or Shift Enter as an alternate way to move through
all the found cells.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"pcor" wrote:

I have a large spreedsheet from L2 to BE 1268
The majoority of the cells contain data. It appears that not all the data is
NUMERIC as I am getting VALUE errors. I would like a macro that would check
all the cells to see which one are NOT numeric so that I could change them to
MUNERIC and avoid all the VALUE errors I am getting
Going accross from l to be I need 4 different totals( I total col L,M,N,s,t u
an other total might total the following col O,P,Q,R etc etc
Thanks