+ Reply to Thread
Results 1 to 4 of 4

Find the Format of a cell

  1. #1
    Chris
    Guest

    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. #2
    Ron Rosenfeld
    Guest

    Re: Find the Format of a cell

    On Thu, 16 Jun 2005 15:06:05 -0700, "Chris" <[email protected]>
    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. #3
    Chris
    Guest

    Re: Find the Format of a cell

    Excellent, Thanks Ron

    -Chris

    "Ron Rosenfeld" wrote:

    > On Thu, 16 Jun 2005 15:06:05 -0700, "Chris" <[email protected]>
    > 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. #4
    Ron Rosenfeld
    Guest

    Re: Find the Format of a cell

    On Fri, 17 Jun 2005 04:52:03 -0700, "Chris" <[email protected]>
    wrote:

    >Excellent, Thanks Ron
    >
    >-Chris


    You're welcome. Glad to help.

    --ron

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1