Excel Macro

January 7, 2009 6:16 AM

Using Excel VBA, how to convert positive numbers to negative, and vice versa?

What I want is:
1. Select a range of data;
2. Click the button which is assigned to the Macro;
3. Positive figures in the selected range will turn to negative and vice versa.

OK. I know maybe it is a simple question, but I urgently need this functions while I'm still a newbie to VBA. Anyone can help to write the code? Thanks a lot.

posted by yezimary to computers & internet (16 answers total)

dunno the syntax, but basically, you just want A2=-A2, right?
posted by notsnot at 6:35 AM on January 7, 2009

Multiply each number in the selected range by -1.
posted by DWRoelands at 6:47 AM on January 7, 2009

The "dirty" way I do this is to set up a new column with a x=y*-1 equation. This is sort of a pain, though.
posted by lunasol at 6:48 AM on January 7, 2009

Thanks you all.

I want a little more:
- A button to click which will impact on all selected cells (maybe not adjacent to each other)
- Only change the Positive/Negative status but keep the original contents unchanged, i.e. A1 cell is "=C1*(D1+E1)" before and turn to "=-C1*(D1+E1)" after.
posted by yezimary at 6:57 AM on January 7, 2009

Is there a blend of formulas and numbers? Or all of one type? Is there the possibility of text in a cell?
posted by teabag at 7:28 AM on January 7, 2009

Give this a go (you may want to filter out text or other values as teabag says):

Private Sub CommandButton1_Click()
    On Error GoTo theEnd
    Dim cell As Range
    For Each cell In Selection.Cells
        ' per cell
        If Left(cell.Formula, 1) <> "=" Then
            ' not a formula, simple multiplier
            cell.Formula = "=-1*" & cell.Formula
        Else
            ' formula, strip `=` and add parens
            cell.Formula = "=(" & Right(cell.Formula, Len(cell.Formula) - 1) & ")*-1"
        End If
    Next cell
theEnd:
End Sub

posted by nz_kyle at 7:30 AM on January 7, 2009

Ultimately you're going to do something like the following:


Sub Foo()
For Each c in Selection
c.Formula = c.Formula & "*-1"

Next
End Sub


Maybe that's enough to get you started. Then just link that to a button.
posted by teabag at 7:32 AM on January 7, 2009

This will do what you want:

Sub FlipSign()

    For Each Cell In Selection
        If Left(Cell.FormulaR1C1, 1) = "=" Then
            Cell.FormulaR1C1 = Cell.FormulaR1C1 & "*-1"
        Else
            Cell.FormulaR1C1 = "=" & Cell.FormulaR1C1 & "*-1"
        End If
    Next Cell

End Sub

Note it gets a little wonky if you run it multiple times on the same cells, since it just keeps adding "*-1" to the formula.
posted by odinsdream at 7:35 AM on January 7, 2009

I would take this approach which will just toggle the selection:

Sub Flip()
Dim c as range

on error goto endmacro
For Each c In Selection.cells
c.value = c.value * -1
Next c

EndMacro:
End Sub

Throw in a
If isnumeric(c.value) Then
End if

If you want to only convert negative to positive
posted by NailsTheCat at 12:50 PM on January 7, 2009

Tarnation. Here's a more robust version:

 
Sub Flip()
Dim c as range

  If TypeName(Selection) <> "Range" Then Exit Sub

  on error resume next
  For Each c In Selection.cells
     If isnumeric(c.value) Then
         c.value = c.value * -1
     End if
  Next c

EndMacro:
End Sub

Throw in a If c.value < 0 if you want to only convert negative to positive. And you may want to check to skip dates also.
posted by NailsTheCat at 4:29 PM on January 7, 2009

I've tried all codes and found the first one works. Thank you, nz_kyle!

nz_kyle, the function of your codes totally met the expectation. Only it will add a lot of "*-1" while clicking more than one time. I wish I can learn the VBA quickly and get it work better ^_^

Odinsdream, your codes generally ok but the parents are missing so it doesn't work for a formula (like "=C1+D1").

NailsTheCat, your codes are cool to ignore the non-numeric cells, but sadly it doesn't keep the original formula.

Anyway, thanks you all. You guys are cool! (For me, everyone who writes code is cool.)
posted by yezimary at 5:00 AM on January 8, 2009

I combined some of the code the others had put together and threw in a bit that converts it back to the original formula if it is run multiple times on the same cell, this will stop it from having a long list of *-1's. Also I included the code to check for numeric values so it won't effect text cells. I think this will cover everything you were looking for. Also the credit for most this code goes to nz_kyle and nailsthecat.

Private Sub CommandButton1_Click()

On Error GoTo theEnd

Dim cell As Range

For Each cell In Selection.Cells

' per cell
If IsNumeric(cell.Value) Then
'check for text

If Left(cell.Formula, 1) <> "=" Then

' not a formula, simple multiplier

cell.Value = cell.Formula * -1


Else
If Right(cell.Formula, 3) = "*-1" Then
cell.Formula = "=" & Mid(cell.Formula, 3, Len(cell.Formula) - 6)

'undoes everything in the step below if ran multiple times
Else
' formula, strip `=` and add parens
cell.Formula = "=(" & Right(cell.Formula, Len(cell.Formula) - 1) & ")*-1"
End If
End If
End If
Next cell

theEnd:

End Sub

posted by mrpeach at 10:57 AM on January 8, 2009

Give this one a try; I've added NailsTheCat's robustness and changed it so it removes the ()*-1 if needed:

Private Sub CommandButton1_Click()
    On Error GoTo theEnd
    ' If TypeName(Selection) <> "Range" Then Exit Sub
    Dim cell As Range
    
    For Each cell In Selection.Cells
        ' per cell
        If IsNumeric(cell.Value) Then
            If Left(cell.Formula, 1) <> "=" Then
                ' not a formula, simple multiplier
                cell.Formula = "=" & cell.Formula & "*-1"
            Else
                ' formula
                If Right(cell.Formula, 4) = ")*-1" Then
                    ' it's already been changed so remove the parens and `*-1`
                    cell.Formula = "=" & Mid(cell.Formula, 3, Len(cell.Formula) - 6)
                ElseIf Right(cell.Formula, 3) = "*-1" Then
                    ' was originally a static number
                    cell.Value = Mid(cell.Formula, 2, Len(cell.Formula) - 4)
                Else
                    ' default value, strip `=` and add parens
                    cell.Formula = "=(" & Right(cell.Formula, Len(cell.Formula) - 1) & ")*-1"
                End If ' already been changed test
            End If ' formula test
        End If ' numeric test
    Next cell

theEnd:
End Sub

posted by nz_kyle at 10:58 AM on January 8, 2009

beaten by a couple of seconds!
posted by nz_kyle at 11:04 AM on January 8, 2009

Ooops!! I hadn't twigged that you were working with formulae. Doh!

To redeem myself here's one additional item you may like to add to the lovely code provided by the others:

Dim cell as range
Dim mySelection as range

Set mySelection = Intersect(Selection, Selection.Parent.UsedRange)

Then use For each cell in mySelection.cells instead

Why do this? Well, let's say you just want to process all the data in a column. If you select that column(s) by clicking on the header (rather than selecting the specific cells themselves), the code will process all 65536 cells, which can take a while. Doing the above means you'll greatly reduce the cells considered.
posted by NailsTheCat at 11:17 AM on January 8, 2009

Awesome!!!

mrpeach + NailsTheCat and nz_kyle + NailsTheCat are both working very well.

The importance of NailsTheCat's above code is to make the function work while selecting a whole column. Without the little amendment, the code doesn't work at all rather than working slowly.
posted by yezimary at 6:46 AM on January 9, 2009