Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Numberformat with many columns at one go
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2016
    Posts
    60
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Numberformat with many columns at one go

    Hello

    I want to numberformat with many columns at one go

    Can i

    Code:
    ws.Columns(3, 5, 9, 11, 23, 27).NumberFormat = Trim(Replace(Format(String(Len(Int(Cell.value)) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
    Also Cell.value in red requires proper syntax
    Will the above format round off if "No" then how can i round off


    Thanks
    SamD
    Last edited by SamDsouza; Jun 20th, 2019 at 10:43 PM.

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,645
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Numberformat with many columns at one go

    Hi Sam

    I've seen this number format in posts for currency in India.

    If you want to use a different numberformat for each cell you'll have to loop through all the cells and add the format. This is not the usual solution. Also if some number would change you'd have to set the format again.


    The usual solution is to use conditional formatting, adding conditions for different number brackets, for ex.

    - for <1000 use "0.0",
    - for <100000 use "0,000.00"
    - for <10000000 use "0\,00\,000.00"
    etc.

    This way you can do it in 1 go for all the columns.
    Also if a number changes the format updates automatically.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    Board Regular
    Join Date
    Apr 2016
    Posts
    60
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Numberformat with many columns at one go

    pgc01

    Sorry for very late reply.
    do you mean something like this

    Code:
    dim nos as Integer
    if nos < 1000
    ws.Columns(3, 5, 9, 11, 23, 27).NumberFormat = Trim(Replace(Format(String(Len(Int(Cell.value)) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".0"
    ElseIf nos < 100000
    ws.Columns(3, 5, 9, 11, 23, 27).NumberFormat = Trim(Replace(Format(String(Len(Int(Cell.value)) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & "0\,00\,000.00"
    End If
    Also i was having doubt using the following syntax for columns at one go. Is the syntax right
    Code:
    ws.Columns(3, 5, 9, 11, 23, 27).NumberFormat
    Frankly speaking i am posting this post where no MS-Excel is installed. Need to check when i am on my desk

    Thanks for the suggestion
    Samd

  4. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,645
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Numberformat with many columns at one go

    Hi Sam

    No, I mean Conditional Formatting.

    I don't have excel here but you can try record a macro while setting the conditional formatting manually and go from there.

    If you have problems post back and I'll post some code tonight (GMT).
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,645
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Numberformat with many columns at one go

    This is an example.

    Open a new workbook and run this code.

    It sets your format in some columns in Sheet1.

    Write some numbers and see how they are formatted.

    Try:

    Code:
    Sub NumberFormatColumns()
    Dim ws As Worksheet
    Dim r As Range
    
    Set ws = Worksheets("Sheet1")
    
    With ws
        Set r = Union(.Columns("A"), .Columns("E"), .Columns("G"))
    End With
    
    With r.FormatConditions
       
        .Delete
       
        .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=1000").NumberFormat = "0.00"
    
        .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=100000").NumberFormat = "0\,000.00"
    
        .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=10000000").NumberFormat = "0\,00\,000.00"
    
        .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=1000000000").NumberFormat = "0\,00\,00\,000.00"
    
        .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=100000000000").NumberFormat = "0\,00\,00\,00\,000.00"
    
        .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=10000000000000").NumberFormat = "0\,00\,00\,00\,00\,000.00"
    
    End With
    
    End Sub
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  6. #6
    Board Regular
    Join Date
    Apr 2016
    Posts
    60
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Numberformat with many columns at one go

    Pgc01

    Thanks i tried it works beautifully.
    But what i desire is when any numeric value typed in cols A, E and G want to round off to nearest value
    as per your solution i typed in col A 134.03 cell values displayed as 134.03 instead of 134

    and if cell value is 134.50 then it should display 135. Any possibilities

    Thanks
    SamD
    Last edited by SamDsouza; Jun 27th, 2019 at 10:18 PM.

  7. #7
    Board Regular
    Join Date
    Apr 2016
    Posts
    60
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Numberformat with many columns at one go

    Sorry But how will Union help ?

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,779
    Post Thanks / Like
    Mentioned
    88 Post(s)
    Tagged
    31 Thread(s)

    Default Re: Numberformat with many columns at one go

    Quote Originally Posted by SamDsouza View Post
    I want to numberformat with many columns at one go

    Can i

    Code:
    ws.Columns(3, 5, 9, 11, 23, 27).NumberFormat = Trim(Replace(Format(String(Len(Int(Cell.value)) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
    Also Cell.value in red requires proper syntax
    Will the above format round off if "No" then how can i round off
    I think you may find my mini-blog article here to be of some interest...

    http://www.excelfox.com/forum/showth...mma-Separation
    Last edited by Rick Rothstein; Jun 28th, 2019 at 01:10 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    Board Regular
    Join Date
    Apr 2016
    Posts
    60
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Numberformat with many columns at one go

    Thanks Rick the same format i;ve adopted from .
    http://www.excelfox.com/forum/showth...mma-Separation

    in post #1 of this thread. Really Good One
    only now i require to round off the nearest value after decimal

    SamD

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,779
    Post Thanks / Like
    Mentioned
    88 Post(s)
    Tagged
    31 Thread(s)

    Default Re: Numberformat with many columns at one go

    Quote Originally Posted by SamDsouza View Post
    Thanks Rick the same format i;ve adopted from .
    http://www.excelfox.com/forum/showth...mma-Separation

    in post #1 of this thread. Really Good One
    only now i require to round off the nearest value after decimal
    Remove what I show in red below from this line of code from inside the For..Next loop
    Code:
        C.NumberFormat = Trim(Replace(Format(String(Len(Int(C.Value)) - 1, "#"), _
                         " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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