Web Development

Ask something
Discussion Locked

Macro for Excel

0 helpful
7 replies

by windlej

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have an excel spreadsheet with a list of dollar amount. The negative numbers on this sheet appear as 88888-. I want to change them to -88888. I already tried to reformat and nothing happins. Does anyone know of a macro that can do this?

7 replies

by dklippert

Your entries are probably text. One way to convert them is to type the number 1 in an empty cell. Copy it and then select the data range. Now go to Edit>Paste Special and choose multiply. At this point your formatting will work just fine.

by dklippert

My bad (as the kids say) My first answer was worong.
Here's a macro I picked up somewhere that will do what you want:

Sub RemoveTrailingMinus()
On Error Resume Next
Dim cell As Range
Dim TempVal As String
With Worksheets("Sheet1")
For Each cell In .Cells. _
SpecialCells(xlCellTypeConstants, xlTextValues)
TempVal = cell.Value
If Right(TempVal, 1) = "-" Then
cell.Value = Left(TempVal, _
Len(TempVal) - 1) * 1
Else
cell.Value = TempVal * 1
End If
Next
End With
End Sub

by dklippert

And one more piece of errata. In the macro above, subsitute this line

Len(TempVal) - 1) * -1

That restores the number to its negative attitude.

by goldenjl

Make sure the formatting is on a valid number category (eg. General, Number, Currency, Accounting) then try to either reenter the number with the minus in front of the number or remove the minus and put parentheses around the number

This discussion is closed to new replies.