Solved

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

Posted on 2011-09-30
7
1,463 Views
Last Modified: 2012-05-12
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

Open in new window

0
Comment
Question by:jackadmin
7 Comments
 
LVL 24

Expert Comment

by:StephenJR
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

by:kgerb
ID: 36893613
How about this.  I agree, be careful about putting this in the change event.

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

Open in new window

0
 

Author Comment

by:jackadmin
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 41

Accepted Solution

by:
dlmille earned 125 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

Open in new window


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

Open in new window

 
   
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 41

Expert Comment

by:dlmille
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

by:jackadmin
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 41

Expert Comment

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

Dave
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Find out how to use Active Directory data for email signature management in Microsoft Exchange and Office 365.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now