Quick way to change negative #s to positive

[Guest]

Alot of times at work I need to take a column of numbers and change them to
either postive or negative numbers. I would like to be able to select the
cells I want then click a button to change the signs of all the cells I
selected. Is there such a button, like a +/- button?? If not is the fastest
way to do this I can find is to select the cells, hit ctrl H and replace the
- with blank.

 

[Guest]

I'm not sure if you want to reverse the sign of each number, or simply
convert all numbers to say, positive, even if some already are positive. This
gives you 3 options:

Sub ChangeSign()

Dim cell As Range
Dim rng As Range
Dim iChoice As Integer
Dim strMsg As String

On Error GoTo NoRangeFound
Set rng = Selection.SpecialCells(xlCellTypeConstants, 1)

strMsg = "Type in 1 to convert to all pos., 2 for all neg., or 3 " & _
"to reverse the sign of each value."

iChoice = Application.InputBox(strMsg, , , , , , , 1)

If iChoice <> 1 And iChoice <> 2 And iChoice <> 3 Then
MsgBox "Not a valid number."
Exit Sub
End If

For Each cell In rng
With cell
If iChoice = 1 And .Value < 0 Or _
iChoice = 2 And .Value > 0 Or _
iChoice = 3 Then
.Value = .Value * -1
End If
End With
Next
Exit Sub

NoRangeFound:
MsgBox "No numbers found in range!"

End Sub

 

[Don Guillett]

how about
Sub replaceminuw()
Selection.Replace What:="-", Replacement:="", LookAt:=xlPart
End Sub