View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Gary''s Student Gary''s Student is offline
external usenet poster
 
Posts: 11,058
Default Numbers treated as text

Thanks, I dim'ed Count in my version as well.

see:

http://www.mvps.org/dmcritchie/excel/toolbars.htm

for "buttonology"
--
Gary''s Student - gsnu200717


"Jack Sheet" wrote:

Thanks - that worked (after including also Dim Count as Long).

Next question:
Is there some way that I can create a button on a toolbar that is
independent of the workbook, so that clicking on the button would run this
macro on the active worksheet? Sorry this is probably beginner stuff.

"Gary''s Student" wrote in message
...
Sub numerify()
Dim r As Range
Count = 0
For Each r In ActiveSheet.UsedRange
If Application.IsText(r.Value) Then
If IsNumeric(r.Value) Then
r.Value = 1# * r.Value
r.NumberFormat = "General"
Count = Count + 1
End If
End If
Next
MsgBox (Count & " cells changed")
End Sub

--
Gary''s Student - gsnu200717


"Jack Sheet" wrote:

Sorry folks I feel that this has to be old ground I am revisiting.

I have a third party application that claims to export reports in XLS
format.
It seems to work as intended with one irritating exception.
Some of the numbers in the exported file are treated as text.
If I highlight the cell and check the cell format it claims to be
formatted
as "General".
But if I enter in another blank cell the formula
=ISNUMBER(test_cell_ref)
it returns FALSE even though the entry in test_cell_ref appears to be
numeric.
Indeed if I select that cell and hit F2 to edit the cell and then it
enter
without making any changes then the above formula thereafter returns
TRUE.
I have had this problem before, usually in relation to exported dates,
but
until now have not encountered it with simple decimal numbers.

I am looking for a solution that will "audit" the workbook for instances
of
cells whose text/number property would change by the action of hitting F2
followed by enter, without any other editing.

This has to be a wheel already invented, assuming that I am not alone in
observing this phenomenon. There may be a compiled add-in available that
does not require a VBA module, but failing that a VBA solution would be
better than nothing.

Any help out there? Thanks.

--
Return email address is not as DEEP as it appears