Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Change Positive Values to Negative Values based cell value - Excel Macro

Posted on 2011-09-30
Medium Priority
1,806 Views
I need to be able to change all positive values into negative values in a range based upon the value of a field in each row.  For example, a row contains a cell (column E) that is either "T3" or "T7".  Also in that row are 6 cells containing numbers.  If the E column is "T7", I want all the values of the cells in F-K to be negative.  I also don't want it to reverse the value, so multiplying the value times -1 won't work.

The code below almost works.  I just need to know the correct syntax to change so that it doesn't change the entire range (F9:K202) when it matches "T7" on one particular row.  It just needs to change the positive to negatives for that row if that row is a T7 match.
``````Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range
Dim t As Range

For Each t In Range("E9:E202")
If t.Value = "T7" Then
For Each r In Range("F9:K202")
If r.Value > 0 Then r.Value = -r.Value
Next r
End If
Next t
End Sub
``````
0

LVL 24

Expert Comment

ID: 36893249
Instead of F9:K202 you could use t.offset(,1).resize(,6), but don't put this in the sheet change event because it will fire continually.
0

LVL 12

Expert Comment

ID: 36893613

Kyle
``````Sub ChangeToNeg()
Dim r As Range
Dim t As Range
For Each t In Range("E9:E202")
If t.Value = "T7" Then
For Each r In Application.Intersect(Rows(t.Row), Columns("F:K"))
If r.Value > 0 Then r.Value = -r.Value
Next r
End If
Next t
End Sub
``````
0

Author Comment

ID: 36894384
Where/How should I invoke this if I don't use a change event?  I want the value to change to negative whenever they type anything in the cell.  I don't want to have to use a button or anything like that.
0

LVL 42

Accepted Solution

dlmille earned 500 total points
ID: 36899074
You can put it in the change event, you just need to turn events off before making the call to the routine, then turn events back on..

e.g.,  using kgerb's code

``````Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range
Dim t As Range

On Error GoTo errHandler
Application.EnableEvents = False 'avoid triggering Worksheet_Change while programmatically changing the worksheet!

For Each t In Range("E9:E202")
If t.Value = "T7" Then
For Each r In Application.Intersect(Rows(t.Row), Columns("F:K"))
If r.Value > 0 Then r.Value = -r.Value
Next r
End If
Next t

GoTo gracefulExit

errHandler:
MsgBox "ERR: " & Err.Number & " ErrDesc: " & Err.Description, vbCritical

gracefulExit:
Application.EnableEvents = True 'ensure events work before leaving the routine

End Sub
``````

PS - this code seems a bit intensive.  Based on any worksheet change, this code will run.  Can you not screen your entries to ensure that the rest of the code (and the loop of comparisons) really need to be run?  Are you trying to correct (change to negative when a positive is typed, but the flag T7 is in column E for that row, so the code would automatically correct your entry)?  Or, are you trying to correct the data by changing to T7 or T3?

With a bit more knowledge about the actions you are taking that would be affecting this "corrective" code, the event could be simplified so it would be less process intensive.

Let's assume you only need to check to ensure values are negative on the row where the user is making changes, if T7 in column E, only - and, those changes could be in column E (so convert everything in F:K to negative (when positive) if a T7 was entered, same row) or those changes could be somewhere in F9:K202 and you want the worksheet_change event to ensure the entry is corrected if a T7 exists in column E, same row as data entry.

As a result, you could use the following code which is less process intensive, rather than have it run everytime a change is made anywhere in the spreadsheet.  I just had a hunch that perhaps this is more focused on your ultimate outcome.  If I'm mistaken, just go with my first code response.

``````Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range
Dim t As Range

On Error GoTo errHandler

For Each Rng In Target 'note, changes may have been made to > 1 cell as a result of copy/paste or delete on more than one selection
'first: check to see if changes were made in column E, from row 9 to 202, OR changes were made in columns F:K, from row 9 to 202
If Not Intersect(Rng, Range("E9:E202")) Is Nothing Or Not Intersect(Rng, Range("F9:K202")) Is Nothing Then
'need to check if T7 was entered
If Range("E" & Rng.Row).Value = "T7" Then
Application.EnableEvents = False
'ensure positive results
For Each r In Intersect(Rows(Rng.Row), Columns("F:K"))
If r.Value > 0 Then r.Value = -r.Value
Next r
End If
End If
Next Rng

GoTo gracefulExit

errHandler:
MsgBox "ERR: " & Err.Number & " ErrDesc: " & Err.Description, vbCritical

gracefulExit:
Application.EnableEvents = True 'ensure events work before leaving the routine

End Sub
``````

I've attached a dummy worksheet to test out this last piece of code.  Download it and check it out.

Cheers,

Dave
changeNegativeIfT7-r1.xlsm
0

LVL 42

Expert Comment

ID: 36899079
PS - line 10 of my second code post, above, can also be written:

If Not Intersect(Rng, Union(Range("E9:E202"), Range("F9:K202"))) Is Nothing Then 'if there was a change in column E, F:K, rows 9:202, then process the test/make changes accordingly

Cheers,

Dave
0

Author Closing Comment

ID: 36900666
Dave, is your two different line 10 examples just a different syntax for the same result or should I have seen different behavior?  I didn't notice any behavior difference, so I assume it was just a different way to write the code.

This worked perfectly for what I needed and you were exactly right on your hunch.  I merely wanted to make sure a user could only enter negative values if the record was a T7 record.  The T7 part would be a locked field, so only input would be the numbers which has to be negative if it is a T7.

Thank you.  The example spreadsheet was above and beyond but definitely helpful.
0

LVL 42

Expert Comment

ID: 36900706
Yes - just two different ways to do line 10.

Dave
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If something goes wrong with Exchange, your IT resources are in trouble.All Exchange server migration processes are not designed to be identical and though migrating email from on-premises Exchange mailbox to Cloudâ€™s Office 365 is relatively simpleâ€¦
With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, itâ€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the adminâ€¦
###### Suggested Courses
Course of the Month10 days, 10 hours left to enroll