? Object, Method, Property to evaluate cell contents

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(ActiveSheet.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

Relevant Pages

  • Easily resolved
    ... 'Text' formatted cells displaying numbers in scientific format ... I use Excel to view statement information downloaded from my bank in CSV ... some cells still display the numeric string in scientific ... Selecting the cell shows that it's already correctly formatted. ...
    (microsoft.public.excel)
  • Re: Actual solution
    ... Then format the cells as text ... some cells still display the numeric string in scientific ... Selecting the cell shows that it's already correctly formatted. ... Microsoft Excel MVP ...
    (microsoft.public.excel)
  • Re: Can General format numbers be mistaken as text?
    ... how many digits that may be. ... It's fine if you want to use General format. ... Perhaps your coworker is worried that there may be situations where Excel will change the cell format from General to Text automagically, just as it changes from General to Number, Currency or Percentage. ... mean it will "display whatever the user types" exactly as it is entered. ...
    (microsoft.public.excel.misc)
  • Re: paste HTML with date different in VBA from manual, format error
    ... If the date is always read into the same cell ... you could try using your VBA to insert a ... into a user-specified date format. ... > button to display the required period, then copy/paste into excel, I ...
    (microsoft.public.excel.programming)
  • RE: custom number formats - including text from other cells
    ... The VBA code can be run as part of your button's Click event, ... expect your end users to change (the Target param of the Change event tells ... But, in fact, I don't think you have to use the Number Format feature ... ' fill in the Risk id:s for every cell. ...
    (microsoft.public.excel.programming)