#1
|
|||
|
|||
Need Macro to Convert Numbers from Positive to Negative
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. Code:
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 |
#2
|
|||
|
|||
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 Code:
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
rsrasc,
You are very welcome and happy hunting... https://www.mrexcel.com/forum/excel-...-question.html |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need Macro to Convert Text To Numbers in Multiple Sheets | rsrasc | Excel Programming | 4 | 04-11-2016 06:21 AM |
Excel VBA to convert number to negative | dakm63 | Excel Programming | 1 | 05-26-2015 10:49 AM |
Table cells with negative numbers being displayed in two lines | HankBrandenburg | Word Tables | 1 | 02-17-2014 03:04 PM |
Custom formatting to make negative numbers show w/o negative sign on charts | todor | PowerPoint | 3 | 01-16-2013 03:45 AM |
How to add the plus sign (+) automatically for the numbers which are positive? | Jamal NUMAN | Excel | 2 | 05-01-2011 03:15 PM |