+ Reply to Thread
Results 1 to 5 of 5

substitute "." with "" and give format

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-18-2008
    Location
    Slovakia
    MS-Off Ver
    Office 2016
    Posts
    162

    substitute "." with "" and give format

    Hi,
    pls. can you help me?
    what is wrong in this code?
    i want in all selected number substitute "." with blank, multiply all selected cells with 1 and give this number format #,##0.00

    but it works only for last selected cell
    pls. what is wrong?

    Sub form_number()
    '
    ' Macro recorded 2.3.2009 by Gálisová
    
               
    Const erate = 1
    Dim cell As Range
    Application.ScreenUpdating = False
    For Each cell In Selection
    
    Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    Rem Cells.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    rem         xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    
        If IsNumeric(cell.Value) And LenB(cell.Value) Then cell.Value = cell.Value * erate
    Next cell
    Application.ScreenUpdating = True
           
     Selection.NumberFormat = "#,##0.00"
            
    End Sub
    Last edited by danusko; 03-10-2009 at 03:55 AM.

  2. #2
    Forum Contributor
    Join Date
    12-18-2008
    Location
    Slovakia
    MS-Off Ver
    Office 2016
    Posts
    162

    Re: substitute "." with "" and give format

    I thing i gave it in bad part of forum

    but i solved it my self

    here it is:
    Sub bodkavcisle()
    '
    ' bodkavcisle Macro' Macro recorded 2.3.2009 by Gálisová
              
    Const erate = 1
    Dim cell As Range
    Application.ScreenUpdating = False
    
    For Each cell In Selection
    Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    Next cell
    For Each cell In Selection
    If IsNumeric(cell.Value) And LenB(cell.Value) Then cell.Value = cell.Value * erate
    Application.ScreenUpdating = True
    
    Next cell
    Selection.NumberFormat = "#,##0.00"
            
    End Sub

  3. #3
    Forum Contributor
    Join Date
    12-18-2008
    Location
    Slovakia
    MS-Off Ver
    Office 2016
    Posts
    162

    Re: substitute "." with "" and give format

    hi all,
    pls. now i found it out, that this code doesnt works good. If is in the number only comma without dot, it multiply the number by 100
    I want convert for example number
    10.205.356,46
    to
    10 205 356,46


    What is wrong in this code please?
    in atach is example the RED number convert wrong

    Sub dot()
    '
             
    Const erate = 1
    Dim cell As Range
    Application.ScreenUpdating = False
    
    For Each cell In Selection
    Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    Next cell
    For Each cell In Selection
    If IsNumeric(cell.Value) And LenB(cell.Value) Then cell.Value = cell.Value * erate
    Application.ScreenUpdating = True
    
    Next cell
    Selection.NumberFormat = "#,##0.00"
            
    End Sub
    Attached Files Attached Files
    Last edited by danusko; 03-04-2009 at 10:14 AM.

  4. #4
    Forum Contributor
    Join Date
    12-18-2008
    Location
    Slovakia
    MS-Off Ver
    Office 2016
    Posts
    162

    Re: substitute "." with "" and give format

    please can you help me with this code?
    thank you

  5. #5
    Forum Contributor
    Join Date
    12-18-2008
    Location
    Slovakia
    MS-Off Ver
    Office 2016
    Posts
    162

    Re: substitute "." with "" and give format

    I wrote this problem in other forum too, because nobody here help me.
    It is on link:
    http://www.vbaexpress.com/forum/show...824#post177824
    thx.

+ 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