2

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
  • 2
    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
4

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
1

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]

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.