Excel Macro
January 7, 2009 6:16 AM Subscribe
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.
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.
Multiply each number in the selected range by -1.
posted by DWRoelands at 6:47 AM on January 7, 2009
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
posted by lunasol at 6:48 AM on January 7, 2009
Response by poster: 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
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
posted by teabag at 7:28 AM on January 7, 2009
Best answer: Give this a go (you may want to filter out text or other values as teabag says):
posted by nz_kyle at 7:30 AM on January 7, 2009
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
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
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
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:
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
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
Response by poster: 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
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
Best answer: 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
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:
posted by nz_kyle at 10:58 AM on January 8, 2009
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
Best answer: 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
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
Response by poster: 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
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
This thread is closed to new comments.
posted by notsnot at 6:35 AM on January 7, 2009