Results 1 to 5 of 5
  1. #1
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing format problem (Excel 2000)

    I only get that result if the cell format is "General" before VBA code stores "10-1" into the cell. If the cell is formatted as Text, then the cell displays "10-1". Could you post a workbook that demonstrates the problem?
    Legare Coleman

  2. #2
    New Lounger
    Join Date
    Jun 2005
    Location
    Connecticut, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    changing format problem (Excel 2000)

    I am having a format problem where after I initiate a vba onclick event that changes the value of a range of cells, the number format is originally set to text, but changes to date on certain cells. An example is of the data in the cell is:
    " 10-1"
    should equal(no leading character):
    "10-1"
    but equals:
    "1-Oct"
    Aside from not allowing the format to change I would also like to add a line of vba code that changes the number format of the cells to text.

  3. #3
    New Lounger
    Join Date
    Jun 2005
    Location
    Connecticut, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing format problem (Excel 2000)

    The File is kind of big, so I am attaching a small simplified version. What I am trying to allow the user to do is:
    1. Copy and Paste values from other spread sheets (Original Value) into Column A of the spreadsheet.
    2. Click the Format button to autoformat the data.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: changing format problem (Excel 2000)

    Another case of Excel being too smart for its own good. Try the following code instead:

    Sub button1_Click()
    Dim oCell As Range
    For Each oCell In Worksheets("Sheet1").Range("A2:A4").Cells
    oCell.NumberFormat = "@"
    oCell.Value = Replace(oCell.Value, " ", "")
    Next oCell
    End Sub

  5. #5
    New Lounger
    Join Date
    Jun 2005
    Location
    Connecticut, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing format problem (Excel 2000)

    Thanks for the help,that worked perfectly.

Posting Permissions

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