? Object, Method, Property to evaluate cell contents

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

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
 
maybe typename()

With Range("a1")
.NumberFormat = "General"
.Value = 1234
MsgBox TypeName(.Value)
.Value = "'1234"
MsgBox TypeName(.Value)
.NumberFormat = "@"
.Value = "123"
MsgBox TypeName(.Value)
End With

or maybe:

if application.isnumber(range("a1")) then
'it's really a number

But just a question: Since you're using this in your intersect() line:
Cells.SpecialCells(xlCellTypeBlanks)

doesn't that limit myRange to just blank cells--and blank cells in vba really
means blank--no formula and no value.
 
Hello Dave!

I did not think that anyone was going to reply so I had not checked back.

That said, I did come up with a solution below.

Originally, this routine was developed to add rows and fill the cells with data/formulas, and the
format, from the preceding cell.

The major challenge was XL's inability to change the display numbers as text without re-entering the
data after formatting the cell as text.

I think that I came up with a solution that appears to work (although I am sure that I did not
anticipate all potential possibilities).

The code that I added is the If statement "If Application.WorksheetFunction.And ...."

Dennis

****************************************************************************

The "final" code became:

Sub FillBlanks()

Dim myLastColumn As Long
Dim myColumnNumber As Long
Dim myRange As Range
Dim myCell As Range

Application.ScreenUpdating = False
Dim myOrigSheetProtectStatus As Boolean
myOrigSheetProtectStatus = ActiveSheet.ProtectContents
If myOrigSheetProtectStatus = True Then
ActiveSheet.Protect UserInterfaceOnly:=True
End If

myLastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count

For myColumnNumber = 1 To myLastColumn
On Error Resume Next
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
If Application.WorksheetFunction.And(Application.WorksheetFunction. _
IsNumber(myCell.Value) = True, myCell.Offset(-1, 0).NumberFormat = "@") _
Then myCell.Value = myCell. Offset(-1, 0).Value
End If
Next myCell

Next myColumnNumber

Range("A1").Select
Application.ScreenUpdating = True
If myOrigSheetProtectStatus = True Then
ActiveSheet.Protect UserInterfaceOnly:=False
End If

End Sub
 
I didn't look at all your code, but VBA has its own And operator:

If Application.IsNumber(myCell.Value) = True _
And myCell.Offset(-1, 0).NumberFormat = "@" Then
myCell.Value = myCell.Offset(-1, 0).Value
End If

(and vba will accept application.worksheetfunction.isnumber as well as
application.isnumber. So I use the version with less typing. (there's other
distinctions with other worksheetfunctions, though.)

And it's pretty rare that a question goes unanswered.

Usually if there isn't a response, it's because people didn't understand the
question (poorly phrased or too arcane) or it would take too long to set up a
test workbook (or just too complex).

But even then, these get a "what do you mean" response.
 
Dave,
Thanks for the info re: And(). I did make an attempt to do it directly in VBA but the code kept
burping until I found the Excel Function. Obviously your information is much better.
 
These here newsgroups are a nice way to learn.
Dave,
Thanks for the info re: And(). I did make an attempt to do it directly in VBA but the code kept
burping until I found the Excel Function. Obviously your information is much better.
Click to expand...
 
Back
Top