Need to Expand a Macro...if possible

samsilverman

Board Regular
Joined
Nov 9, 2009
Messages
166
I am using Excel 2007.

With the help of this forum, I have the following code in a worksheet VB so that anytime I type a number in a column, it shows up as a negative value.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
If Intersect(Target, Range("I:J")) Is Nothing Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
For Each cel In Target.Cells
If cel > 0 Then cel = -cel
Next cel
Application.EnableEvents = True
End Sub

I have a question. How can I expand this to do just the opposite in other select columns. In other words, change any number in "F:G" to a positive number. Is it possible to have a code do both of these actions?
 
Last edited:

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, MyRange As Range
Set MyRange = Intersect(Target, Range("F:G, I:J"))
If MyRange Is Nothing Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
For Each cel In MyRange
    Select Case cel.Column
        Case 9, 10
            If cel > 0 Then cel = -cel
        Case 6, 7
            If cel < 0 Then cel = -cel
    End Select
Next cel
Application.EnableEvents = True
End Sub
 

General Ledger

Active Member
Joined
Dec 31, 2007
Messages
460
How can I expand this to do just the opposite in other select columns. In other words, change any number in "F:G" to a positive number. Is it possible to have a code do both of these actions?

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
If Intersect(Target, Range("I:J")) Is NOT Nothing Then
On Error Resume Next
Application.EnableEvents = False
For Each cel In Target.Cells
If cel > 0 Then cel = -cel
Next cel
ELSE IF Intersect(Target, Range("F:G")) Is NOT Nothing Then
On Error Resume Next
Application.EnableEvents = False
For Each cel In Target.Cells
If cel < 0 Then cel = -cel
Next cel
END IF
Application.EnableEvents = True
End Sub

Best of luck,

General Ledger
 

samsilverman

Board Regular
Joined
Nov 9, 2009
Messages
166
Thanks Jonmo1 and General Ledger. Will try suggestions out in an hour and post reply.

sam
 

bradley7

Board Regular
Joined
Feb 22, 2005
Messages
102
Sam, can't you just multiply it by -1 to change the sign?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Sam, can't you just multiply it by -1 to change the sign?
OP only wants to convert POSITIVE numbers to negative...
There may be BOTH posotive and negative numbers in the range..

Multiplying all by -1 would convert neg to pos AND pos to neg...
OP wants to keep the neg numbers neg, and convert pos to neg


AND vice versa in the other columns...
 

samsilverman

Board Regular
Joined
Nov 9, 2009
Messages
166
JONMO1-

Your formula worked very nicely, but I have a snag. After I paste the code in my template, it acts as expected. Negative values put in columns F & G by accident change to positive and positive values in I & J change to negative.

However, there is a need from time to time to copy and paste the template on down the worksheet so I can have more cells to receive data from the field. But, when I copy and paste the template, ALL the values in the template that originate from a list turn to zero. These values are in the ranges:

F10-G13
I10-J13
L10-M13

The list they come from is on another worksheet and their values depend on 1 of 4 options I select from a drop down list located in I8

Any idea why? I guess one option would be to copy and paste all the templates I think I would need first and then insert the code. But, I'm hoping you have a different, easy fix.

Thanks.
 

samsilverman

Board Regular
Joined
Nov 9, 2009
Messages
166
Disregard the last reply. The error was mine. I forgot the "$" in those range formulas. DOH!

Thanks again.

Of course, the cells in the range of F10-G13 that contain (and must maintain) negative values still convert to positive when I copy and paste. The original isn't ever affected by the code, but the subsequent pasted templates are.
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
You could try this...It will only change the values if ONE cell is changed at a time.
If multiple cells are changed at the same time (via copy paste, or CTRL+ENTER) then it won't run convert the values

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, MyRange As Range
If Intersect(Target, Range("F:G, I:J")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Select Case Target.Column
    Case 9, 10
        If Target > 0 Then Target = -Target
    Case 6, 7
        If Target < 0 Then Target = -Target
End Select
Application.EnableEvents = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,491
Messages
5,468,926
Members
406,620
Latest member
Gitani123

This Week's Hot Topics

Top