Need to Expand a Macro...if possible

samsilverman

Board Regular
Joined
Nov 9, 2009
Messages
176
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:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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