none
convert to numbers RRS feed

  • Question

  • Hi

    This code does not work properly. Small numbers between -1 and 1 wont convert to number-format. I get an error message in each such cell wheter I want to convert to number from text.

    Can I re-code somehow ? I dont understand what I do wrong.

    Best Sverker

    Sub Period_to_comma()

        
        With Worksheets("DATA")
        Columns("E:F").Select
       
        Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
       
        End With
       
        With Worksheets("DATA")
        Columns("E:F").NumberFormat = "#,##0.00"
        End With
    End Sub


    Sverreberre

    Monday, July 2, 2012 2:46 PM

Answers

  • What happens if you run this:

    Sub Period_to_comma()
        With Worksheets("DATA")
            With Intersect(.UsedRange, .Range("E:F"))
                .NumberFormat = "#,##0.00"
                .Value = .Value
            End With
        End With
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by sverreberre Tuesday, July 3, 2012 6:46 AM
    Monday, July 2, 2012 5:47 PM

All replies

  • What exactly are you trying to do?

    Monday, July 2, 2012 3:00 PM
  • HI

    I am trying the following:

    1) replace . with ,

    2) Convert to numberformat with space as thousand-separator.


    Sverreberre

    Monday, July 2, 2012 3:04 PM
  • What is the point of doing this?

    Excel is getting confused when you replace . with , 

    These are not numbers not strings.

    If you do what you say you want, then you need to make the columns strings.

    But then you won't be able to use the numberFormating.


    • Edited by ieee488 Monday, July 2, 2012 3:08 PM
    Monday, July 2, 2012 3:08 PM
  • What happens if you run this:

    Sub Period_to_comma()
        With Worksheets("DATA")
            With Intersect(.UsedRange, .Range("E:F"))
                .NumberFormat = "#,##0.00"
                .Value = .Value
            End With
        End With
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by sverreberre Tuesday, July 3, 2012 6:46 AM
    Monday, July 2, 2012 5:47 PM
  • The default display of number formatting in Excel is determined by the Windows regional settings. If you change the Cell formatting to a text string to reflect local conditions, rather than change it in Windows so that it will retain its number format, you are likely to build in other issues that may need to be addressed.

    Graham Mayor - Word MVP
    www.gmayor.com

    Tuesday, July 3, 2012 5:46 AM