Find the Format of a cell
 

I have a spreadsheet that allows the user to input mostly numeric values but
once in a while will enter a Text value. I run conditional code to do
something if the numeric value falls into a certain range. I need to check
the format of the cell before I apply the condition to make sure it is
numeric.

How do I check the cells format prior to apply the code. Here is what I
tried but kept getting an object required error

For CellNum = 10 To 22
If (["C" & "CellNum"].NumberFormat) = False Then
GoTo SkipCell
Else...

Thanks for all of your help.

-Chris

Find the Format of a cell
 

On Thu, 16 Jun 2005 15:06:05 -0700, "Chris"
wrote:

I have a spreadsheet that allows the user to input mostly numeric values but
once in a while will enter a Text value. I run conditional code to do
something if the numeric value falls into a certain range. I need to check
the format of the cell before I apply the condition to make sure it is
numeric.

How do I check the cells format prior to apply the code. Here is what I
tried but kept getting an object required error

For CellNum = 10 To 22
If (["C" & "CellNum"].NumberFormat) = False Then
GoTo SkipCell
Else...

Thanks for all of your help.

-Chris


1. Your syntax for your range reference is wrong.
2. Since all cells have a NumberFormat property, your condition test will
never evaluate to True.

---------------------

If your intention is to only allow numeric entries, then use data validation.

If your intention is to only run some test on numeric entries, then test to see
if the entry is a number.

-------------------

See how a cell can have the same property yet still contain either numbers or
text:

Sub foo()
Dim CellNum As Long

For CellNum = 10 To 22 Step 2
With Range("C" & CellNum)
.NumberFormat = "0.00"
.Value = "This is a String"
.Offset(1, 0).NumberFormat = "0.00"
.Offset(1, 0).Value = 275
End With
Next CellNum


For CellNum = 10 To 22
Debug.Print _
Range("C" & CellNum).Address & " " & "Format: " & _
Range("C" & CellNum).NumberFormat & " " & "Cell contents: " & _
Range("C" & CellNum).Text
Next CellNum

End Sub

Gives an output of:

$C$10 Format: 0.00 Cell contents: This is a String
$C$11 Format: 0.00 Cell contents: 275.00
$C$12 Format: 0.00 Cell contents: This is a String
$C$13 Format: 0.00 Cell contents: 275.00
$C$14 Format: 0.00 Cell contents: This is a String
$C$15 Format: 0.00 Cell contents: 275.00
$C$16 Format: 0.00 Cell contents: This is a String
$C$17 Format: 0.00 Cell contents: 275.00
$C$18 Format: 0.00 Cell contents: This is a String
$C$19 Format: 0.00 Cell contents: 275.00
$C$20 Format: 0.00 Cell contents: This is a String
$C$21 Format: 0.00 Cell contents: 275.00
$C$22 Format: 0.00 Cell contents: This is a String

---------------------------
All had numeric formats; but some contain text and some numbers.

What you might consider is

IsNumeric(Range("C" & CellNum).Value)

which will return TRUE or FALSE depending on the cell contents.





--ron

Find the Format of a cell
 

Excellent, Thanks Ron

-Chris

"Ron Rosenfeld" wrote:

On Thu, 16 Jun 2005 15:06:05 -0700, "Chris"
wrote:

I have a spreadsheet that allows the user to input mostly numeric values but
once in a while will enter a Text value. I run conditional code to do
something if the numeric value falls into a certain range. I need to check
the format of the cell before I apply the condition to make sure it is
numeric.

How do I check the cells format prior to apply the code. Here is what I
tried but kept getting an object required error

For CellNum = 10 To 22
If (["C" & "CellNum"].NumberFormat) = False Then
GoTo SkipCell
Else...

Thanks for all of your help.

-Chris


1. Your syntax for your range reference is wrong.
2. Since all cells have a NumberFormat property, your condition test will
never evaluate to True.

---------------------

If your intention is to only allow numeric entries, then use data validation.

If your intention is to only run some test on numeric entries, then test to see
if the entry is a number.

-------------------

See how a cell can have the same property yet still contain either numbers or
text:

Sub foo()
Dim CellNum As Long

For CellNum = 10 To 22 Step 2
With Range("C" & CellNum)
.NumberFormat = "0.00"
.Value = "This is a String"
.Offset(1, 0).NumberFormat = "0.00"
.Offset(1, 0).Value = 275
End With
Next CellNum


For CellNum = 10 To 22
Debug.Print _
Range("C" & CellNum).Address & " " & "Format: " & _
Range("C" & CellNum).NumberFormat & " " & "Cell contents: " & _
Range("C" & CellNum).Text
Next CellNum

End Sub

Gives an output of:

$C$10 Format: 0.00 Cell contents: This is a String
$C$11 Format: 0.00 Cell contents: 275.00
$C$12 Format: 0.00 Cell contents: This is a String
$C$13 Format: 0.00 Cell contents: 275.00
$C$14 Format: 0.00 Cell contents: This is a String
$C$15 Format: 0.00 Cell contents: 275.00
$C$16 Format: 0.00 Cell contents: This is a String
$C$17 Format: 0.00 Cell contents: 275.00
$C$18 Format: 0.00 Cell contents: This is a String
$C$19 Format: 0.00 Cell contents: 275.00
$C$20 Format: 0.00 Cell contents: This is a String
$C$21 Format: 0.00 Cell contents: 275.00
$C$22 Format: 0.00 Cell contents: This is a String

---------------------------
All had numeric formats; but some contain text and some numbers.

What you might consider is

IsNumeric(Range("C" & CellNum).Value)

which will return TRUE or FALSE depending on the cell contents.





--ron

Find the Format of a cell
 

On Fri, 17 Jun 2005 04:52:03 -0700, "Chris"
wrote:

Excellent, Thanks Ron

-Chris

You're welcome. Glad to help.

--ron