In this post we will see how we could invert number in a cell in excel from positive to negative and from negative to positive.
We will use a small excel vba macro code to convert numbers from positive to negative and negative to positive.
Below in column A we could see there are some positive and negative numbers and the final output that we want is in column B i.e. positive number is converted to negative and vice versa.
Step 1
Press Alt+F11 keyboard shortcut to open VB editor in excel or you can click on the VB editor icon in the developer tab in excel.
Step 2
Double click on “ThisWorkbook ” to open the module where we will write the VBA code to convert numbers from positive to negative and vice versa.
Step 3
In this VB editor paste the code below, this code, the code below has three parts , first we have defined the range in which we have the numbers that we want to invert and the second part is looping through each cell in that range and at the last we are checking the value whether they are positive or negative and accordingly changing the cell value from positive to negative and negative to positive.
I have defined the range here as A1:A7, since we have the data from A1 to A7, you can define the range as per you requirement.
Sub InvertNumbers()
Set myrange = Range(“A1:A7”) ‘Set range here
For Each cell In myrange
If cell.Value > 0 Then
cell.Value = -cell.Value
Else
cell.Value = -cell.Value
End If
Next cell
End Sub
Step 4
Now run the program by pressing the green Play button as shown in the fig below.
Step 5
Now after you have run the program, check the column A where we had the original data and now all the positive numbers have converted to negative and negative numbers have been converted to positive.
Hope this helped.