pcor said:
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 said:
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
