Force to Negative Values

Willmonbo

Board Regular
Joined
May 16, 2010
Messages
141
I am dealing with a dataset that contains columns that will always be negative values, at times the person entering the data will in error type it as positive values, so i'd like to have VBA code that would force it to negative values so let's say Columns B, I, and P should always turn to negative even in the event the values were entered as positive.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Right click the sheet tab, select View Code and paste in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
    Case 2, 10, 16
        If Target.Value > 0 Then
            Application.EnableEvents = False
            Target.Value = -Target.Value
            Application.EnableEvents = True
        End If
    End Select
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,638
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Give this macro a try...

Code:
Sub MakeNegative()
  Dim Letter As Variant, LastRow As Long
  Const StartRow As Long = 2
  Const Cols As String = "B I P"
  For Each Letter In Split(Cols)
    LastRow = Cells(Rows.Count, Letter).End(xlUp).row
    With Cells(StartRow, Letter).Resize(LastRow - StartRow + 1)
     .Value = Evaluate("=IF(" & .Address & ">0,-" & .Address & "," & .Address & ")")
    End With
  Next
End Sub
Change the two Const statements to reflect your actual setup (I set StartRow equal to 2 assuming headers, the Cols were set as per your example). Note that Cols is a space delimited string of column letters.
 

Willmonbo

Board Regular
Joined
May 16, 2010
Messages
141
Thank you guy's for the help,

Peter (VoG), I should have indicated that those columns do have headers, it errored out the first time I entered a header and it looks like the code no longer controls the sheet.

Rick does seem to address that in his code but haven't tried it yet.
 

Willmonbo

Board Regular
Joined
May 16, 2010
Messages
141

ADVERTISEMENT

Rick, I just tested your code and that doesn't work either, I am already totally dumbfounded.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,638
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Rick, I just tested your code and that doesn't work either, I am already totally dumbfounded.
It worked in my tests. Are you sure your cells contain "real" numbers and not just text that "looks" like numbers? Can you post a sample workbook showing the code doesn't work so I can look directly at what you have? If so, either use your SkyDrive or, if you don't have it set up, then post it here http://www.box.net/files and report back the URL link they give you to your file.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,638
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I am using dropbox, please click on the following link
https://dl.dropbox.com/u/84586105/VariousNotes.xlsm

this file is just a file where i was testing it, so don't look for logic to what I'm trying to do.

Okay, I downloaded the file, but I don't see numbers in Columns B, I or P that should be changed. Your original post said "so let's say Columns B, I, and P should always turn to negative", hence, I set the code to process those columns. I said as much in my message when I told you "Change the two Const statements to reflect your actual setup (I set StartRow equal to 2 assuming headers, the Cols were set as per your example). Note that Cols is a space delimited string of column letters." This is the statement controlling which columns will have their numbers converted to negative values...

Const Cols As String = "B I P"

Just change the letters B, I, P to whichever column letters you actually want to process (you can put in one, two, three, four, or as many column letters as you have need to chang.
 

Willmonbo

Board Regular
Joined
May 16, 2010
Messages
141
that's why I wrote don't try find logic to the way the file looks, this is merely a scrap file but when I type something in columns B,I,P, i'm still not getting the desired results.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,638
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
that's why I wrote don't try find logic to the way the file looks, this is merely a scrap file but when I type something in columns B,I,P, i'm still not getting the desired results.
Oh, you wanted event code so it happens automatically. Sorry, my misunderstanding... I gave you a macro (which you would have to run manually). Try this event code then...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("B:B,I:I,P:P")) Is Nothing Then
    For Each Cell In Intersect(Target, Range("B:B,I:I,P:P"))
      If IsNumeric(Cell.Value) Then
        If Cell.Value > 0 Then Cell.Value = -Cell.Value
      End If
    Next
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,979
Messages
5,767,432
Members
425,412
Latest member
andrealp4444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top