Force to Negative Values

[Willmonbo]

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.

 

[VoG]

Right click the sheet tab, select View Code and paste in

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]

Give this macro a try...

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]

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]

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

 

[Rick Rothstein]

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.

 

[Willmonbo]

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.

 

[Rick Rothstein]

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]

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]

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...

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.