none
Convert minus values to Bracket values -1.23 => (1.23) RRS feed

  • Question

  • Hi all, I need convert all minus values to bracket values. i have created a vba but i have some doubts. please check my below. i need change in entire worksheet.

    Sub test3()
    If ActiveSheet.Cells.NumberFormat = "-0.00" Then
    ActiveSheet.Cells.NumberFormat.NumberFormat = "(0.00)"
    Else
    End If
    End Sub

    Thursday, July 19, 2018 2:09 AM

Answers

  • Hello Prasanthan Perumal,

    Do you want to make user's newly entered number format automatically? If so, check my last reply.

    If you want to convert exist data format, you could adjust my code to loop through each used cell and set format for them.

    For Each Cel In ActiveSheet.UsedRange
    If IsNumeric(Cel.Value) Then
       If InStrRev(Cel.Value, ".") Then
       DigitsCount = Len(Cel.Value) - InStrRev(Cel.Value, ".")
       DigitsString = ""
       For i = 1 To DigitsCount
       DigitsString = DigitsString & "0"
       Next i
       FormatString = "0." & DigitsString & "_);(0." & DigitsString & ")"
       Cel.NumberFormat = FormatString
       End If
    End If
    Next Cel

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 23, 2018 2:04 AM

All replies

  • Hello Prasanthan Perumal,

    According to my record result, the current number format should be "0.00_);(0.00)".

    Besides, I does not suggest you check cells number format first. The default numberformat is "General" and if any cell's numberformat is different with others, the ActiveSheet.Cells.NumberFormat will return Null.

    If you do need change the format in the entire worksheet, just set it directly.

    Sub Macro2()
    ActiveSheet.Cells.NumberFormat = "0.00_);(0.00)"
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 19, 2018 3:31 AM
  • Thank you Sir! The Problem is When its comes with 3 or 4 digits what will happen? that's why i wrote a macro with if contion. like "-5.453 => (5.453)" could i need to create an other macro? Thank you again Sir!
    Thursday, July 19, 2018 3:47 AM
  • Hello Prasanthan Perumal,

    I would suggest you use WorkSheet_Change event to change the number format dynamically.

    Once we change the value in a cell, the code will try to check the number of digits and adjust the number format.

    For example.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cel As Range
    For Each Cel In Target.Cells
    If IsNumeric(Cel.Value) Then
       If InStrRev(Cel.Value, ".") Then
       DigitsCount = Len(Cel.Value) - InStrRev(Cel.Value, ".")
       DigitsString = ""
       For i = 1 To DigitsCount
       DigitsString = DigitsString & "0"
       Next i
       FormatString = "0." & DigitsString & "_);(0." & DigitsString & ")"
       Cel.NumberFormat = FormatString
       End If
    End If
    Next Cel
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 20, 2018 6:47 AM
  • Hi, all!

    I am trying to record a macro for put brackets for negative values in activesheet.

    Example

    -1.2 => (1.2)

    -1.23 => (1.23)

    -7.456 => (7.456)

    -0.32 => (0.32)

    Is there any one can give me a idea?

    Thanks in Advance!!

    Saturday, July 21, 2018 5:41 AM
  • Thank you sir!

    This is my sheet. I need put brackets around this all values.

    -1.2 => (1.2)

    -1.23 => (1.23)

    -7.456 => (7.456)

    -0.32 => (0.32)

    I have got lots of sheets with different values.

    Thanks in advance!

    Saturday, July 21, 2018 5:57 AM
  • If you want all numbers in all sheets formated the same way in the workbook, modify the 'Normal' style, under number formats, Custom, apply this as the format -

    General;(General)

    If you want thousand separators or say only two decimals, change General to suit, look at the examples under Custom in number formats.

    If you don't want to change 'Normal' as the default Style for your entire workbook, add a new Style with your custom number-format and apply it to cells as required.

    Sunday, July 22, 2018 3:03 PM
    Moderator
  • Hello Prasanthan Perumal,

    Do you want to make user's newly entered number format automatically? If so, check my last reply.

    If you want to convert exist data format, you could adjust my code to loop through each used cell and set format for them.

    For Each Cel In ActiveSheet.UsedRange
    If IsNumeric(Cel.Value) Then
       If InStrRev(Cel.Value, ".") Then
       DigitsCount = Len(Cel.Value) - InStrRev(Cel.Value, ".")
       DigitsString = ""
       For i = 1 To DigitsCount
       DigitsString = DigitsString & "0"
       Next i
       FormatString = "0." & DigitsString & "_);(0." & DigitsString & ")"
       Cel.NumberFormat = FormatString
       End If
    End If
    Next Cel

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 23, 2018 2:04 AM
  • Thank you so much, its working perfectly!
    Monday, July 23, 2018 8:29 AM