? Object, Method, Property to evaluate cell contents

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Dennis (dmburgess_at_removespamameritech.net)
Date: 03/25/04 

Date: Thu, 25 Mar 2004 13:59:48 GMT

XL 2003

In the process of attempting to build a workaround for the number to text issue.

What VBA set of Object, Method & Property will evaluate the type of contents in a
cell?

The following VBA code does exactly what I want EXCEPT for the display of cells
containing numeric data intended to be displayed as text.

    Set myRange = Intersect(Active***.UsedRange, Columns(myColumnNumber), _
              Cells.SpecialCells(xlCellTypeBlanks))
    For Each myCell In myRange
            myCellFormat = myCell.NumberFormat
            myCell.NumberFormat = "General"
            If myCell.Offset(-1, 0).HasFormula Then
                With myCell.Offset(-1, 0)
                    .AutoFill .Resize(2, 1), xlFillDefault
                End With
                myCell.NumberFormat = myCellFormat
            Else
                myCell.FormulaR1C1 = myCell.Offset(-1, 0).FormulaR1C1
                myCell.NumberFormat = myCellFormat
            End If
     Next myCell

I wish to evaluate each cell for the data it contains then re-write the data to the
cell AFTER changing the cell format to text. Further, I want to do this ONLY for
cells that contain numbers.

In short, what VBA code will:

If Evaluate??(myCell) = number Then rewrite " ' " & (Cell contents) to cell

All this would not be necessary if XL would change the format (display) of numbers to
text without having to re-write the contents back into the cell after formatting as
text. Or am I making an inappropriate statement?

TIA Dennis

Quantcast