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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Rick, I just tested your code and that doesn't work either, I am already totally dumbfounded.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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
Back
Top