Need Macro to Convert Numbers from Positive to Negative

[rsrasc]

Hi all,

I have a file with the name "FY 19-Budget (with macro extension).

Within the file, I have three sheets with the following names:

130R
135R
140R

From cell E4 to P45, I have numbers on it.

Some of the cells also have the following values: "0.00"


If possible, I would like to have a macro that will convert the values from positive to negative in all the cells from E4 to P45 (I guess anything greater than zero).

I found the below code but don't know how to use it in my situation.

Sub test() 
For Each Cell In Range("a1:z100") 
If Cell.Value > 0 Then 
    Cell.Value = cell.value * -1 
End If 
Next Cell 
End Sub

To illustrate, for example, in cell E4, under sheet 130R, I have the following value:

1051.00

After conversion, the value in E4 will be, for example:

-1051.00



Any questions, please let me know.


As always, thank you for your support.


Much appreciated,

rsrasc

[jeffreybrown]

Maybe this will help...

Where do I paste the code that I find on the internet

You should also declare variables within your vba procedures.

Define Variables In VBA

Option Explicit

Sub test()
    Dim Cell As Range
    Dim ws As Worksheet
    For Each ws In Worksheets(Array("130R", "135R", "140R"))
        For Each Cell In ws.Range("E4:P45")
            If Cell.Value > 0 Then
                Cell.Value = Cell.Value * -1
            End If
        Next Cell
    Next ws
End Sub

[rsrasc]

Hello Jeffrey,

Thanks for the code.

I have a question for you.

Just wondering if it would it be possible to re-write the code without having to use the array option that you have before the sheet names?

Your code did the trick.

Much appreciated.

Regards,
rsrasc

[jeffreybrown]

Your welcome. Yes, it can be written in other ways. I suppose you are just asking for learning purposes!

You can do a google search for "for each ws in array" and see different options.

There are many factors that go into deciding what to use. How many sheets are there? If only one or two other sheets besides the "130R", "135R", "140R", you could use an if statement construct.

You could also use a case statement. Maybe even Lbound/Ubound structure.

Many different ways to skin this cat.

[rsrasc]

Hi Jeffrey, thanks for info. I will do some more search as you suggested, and yes indeed, I was asking/looking for other alternatives and more importantly for learning purposes.

Again, much appreciated.

Thanks!
rsrasc

[jeffreybrown]

rsrasc,

You are very welcome and happy hunting...

https://www.mrexcel.com/forum/excel-...-question.html