Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find a cell with a particular date format? | Excel Discussion (Misc queries) | |||
Find/Replace changes cell format ?????? | Excel Discussion (Misc queries) | |||
Find partial cell format | Excel Discussion (Misc queries) | |||
find word in a cell then change cell format | Excel Programming | |||
macro to find date format in a cell and delete that entire row | Excel Programming |