About turning negative number into Text

[Kingnight]

Hey,

Thanks a lot for your time. I have one question in VBA:

ex: -1

I am trying to turn this one into (1). Here is my forumula in VBA:

neither of those works.


Cells(1, 1) = Application.WorksheetFunction.Text(Cells(1, 1), "0_);(0)")
cells(1,1) = "("&-cells(1,)&")"

Please help!!!

 

[Jonmo1]

You can try
[/code]
With Cells(1,1)
.Value = .Value * -1
End With
[/code]

 

[Scott Huish]

Is this what you want?

Sub test()
Range("A1").NumberFormat = "0_);(0)"
End Sub

 

[Kingnight]

I want to turn -1 into Text (1) rather than number (1) :. Then I could combinte (1) with other things like ABCD.

Final result will look like: (1)ABCD.

I hope that it is clear~~~

 

[Scott Huish]

Sub test()
With Range("A1")
    If .Value < 1 Then
        .NumberFormat = "@"
        .Value = "(" & Abs(.Value) & ")"
    End If
End With
End Sub

 

[Kingnight]

Thanks!!!!, HOTPEPPER. It works.

I guess that problem about my forumula is that excel will not allow me to use "(" & (this has been to positive number?) & ")"

By the way, what is @ as number format?

Thanks

 

[Scott Huish]

I'm checking to see if the value is less than 0, so it will show it as (1). I'm converting it to a positive number so it doesn't show it as (-1)

@ in a number format is Text.

 

[Kingnight]

Thanks

I guess that given cells(1,1) = -1


Cells(1, 1).Value = "(" & Abs(Cells(1, 1).Value) & ")"


does not work because "(" or ")", for whatever the reason, could be used only if you convert cells(1,1) into TEXT first.

It does not happen to other things like "ABC"&Abs(cells(1,1).value)&"CDE"

Also this only happens in VBA, not in excel.

 

[Scott Huish]

This does happen in Excel, if you only format the cell to show negative numbers in parentheses, when you use it in a concatenation formula it will show -1, not the formatted version of it.

This is what happens if -1 is only formatted to show as (1)

Excel Workbook
	A	B
1	(1)	-1ABC
Sheet1

 

[Kingnight]

Yes. I see.

Thanks a lot for helping me solve this problem Have a nice day!!!