Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-24-2018, 07:21 AM
rsrasc rsrasc is offline Need Macro to Convert Numbers from Positive to Negative Windows 10 Need Macro to Convert Numbers from Positive to Negative Office 2013
Competent Performer
Need Macro to Convert Numbers from Positive to Negative
 
Join Date: Mar 2014
Location: Germany
Posts: 148
rsrasc is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 09-24-2018, 08:40 AM
jeffreybrown jeffreybrown is offline Need Macro to Convert Numbers from Positive to Negative Windows Vista Need Macro to Convert Numbers from Positive to Negative Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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
Reply With Quote
  #3  
Old 09-24-2018, 11:14 AM
rsrasc rsrasc is offline Need Macro to Convert Numbers from Positive to Negative Windows 10 Need Macro to Convert Numbers from Positive to Negative Office 2013
Competent Performer
Need Macro to Convert Numbers from Positive to Negative
 
Join Date: Mar 2014
Location: Germany
Posts: 148
rsrasc is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 09-24-2018, 11:42 AM
jeffreybrown jeffreybrown is offline Need Macro to Convert Numbers from Positive to Negative Windows Vista Need Macro to Convert Numbers from Positive to Negative Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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.
Reply With Quote
  #5  
Old 09-24-2018, 12:32 PM
rsrasc rsrasc is offline Need Macro to Convert Numbers from Positive to Negative Windows 10 Need Macro to Convert Numbers from Positive to Negative Office 2013
Competent Performer
Need Macro to Convert Numbers from Positive to Negative
 
Join Date: Mar 2014
Location: Germany
Posts: 148
rsrasc is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 09-24-2018, 12:38 PM
jeffreybrown jeffreybrown is offline Need Macro to Convert Numbers from Positive to Negative Windows Vista Need Macro to Convert Numbers from Positive to Negative Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

rsrasc,

You are very welcome and happy hunting...

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Macro to Convert Numbers from Positive to Negative Need Macro to Convert Text To Numbers in Multiple Sheets rsrasc Excel Programming 4 04-11-2016 06:21 AM
Need Macro to Convert Numbers from Positive to Negative Excel VBA to convert number to negative dakm63 Excel Programming 1 05-26-2015 10:49 AM
Need Macro to Convert Numbers from Positive to Negative 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
Need Macro to Convert Numbers from Positive to Negative How to add the plus sign (+) automatically for the numbers which are positive? Jamal NUMAN Excel 2 05-01-2011 03:15 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:19 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft