# macro that inverts numbers

I need some help figuring this out. I created a small script that changes a value in a cell into a positive if the number is negative and vice versa. For example if the value is 100 the script will change it to -100 or if it's -100 it changes to 100. I was able to make it work for one cell but now I would like to inplimented on a range of cells for example all cells from B33 to B50. Thanks in advance.

``````Sub Sing_Change()

If Range("E9") > 0 Then
Range("E9") = -(Range("E9"))
Else
Range("E9") = Abs(Range("E9"))
End If

End Sub
``````
• All you need is to negate the value, using `-Range(E9)`: this makes positive numbers negative and negative numbers positive, with no effect on zero. – AFH Nov 10 '17 at 20:52
• Thank you for the response. What I'm stuck in is trying to create a loop with this script that will go down the column starting at B33 to B50. – Eric Nov 10 '17 at 21:02
• What if the cell contains a numeric result out of a formula? – patkim Nov 10 '17 at 21:08
• For a sign change just multiply by -1 ... minus 1... no need to check range or anything. – Solar Mike Nov 10 '17 at 21:12
• @AFH sorry - I saw the code and jumped, now realise you said the same... – Solar Mike Nov 10 '17 at 21:13

Very basic code to loop thru a specified range.

``````Sub test()

Set myrange = Range("A1:A2") 'Set range here

For Each cell In myrange
'your code here e.g. cell.value = -cell.value or whatever
Next cell

End Sub
``````
• I just tried it and it worked. Thanks. – Eric Nov 10 '17 at 21:28

If you want to avoid the loop, you can use a single line with the `[]` brackets, which ask the macro to Evaluate what you want to do. As with all great powers, it comes with great responsibilities.

``````[B33:B50] = [-B33:B50]
``````