Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
lob
 
Posts: n/a
Default switching values from positive to negative

Is it possible to automatically switch positive values to negative
values to entries in columns v to ir just by entering a 1 in either
column q or r on that same row. Conditional formatting?
Thanks in advance, Lob

  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

Lob,

You would need to use the worksheet's change event. For example, if you
enter a 1 into column Q, the code below will change all negatives to
positives in column V to Z. It also allows you to undo your changes by
changing the 1 back to 0.

Copy the code below, right click on the sheet tab, select "View Code" and
paste the code in the window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range

If Target.Column < 17 Then Exit Sub
Application.EnableEvents = False
If Target.Value = 1 Then
For Each myCell In Range("V" & Target.Row).Resize(1, 5)
If myCell.Value < 0 Then myCell.Formula = _
"=-(" & myCell.Formula & ")"
Next myCell
End If
If Target.Value = 0 Then
For Each myCell In Range("V" & Target.Row).Resize(1, 5)
If Left(myCell.Formula, 3) = "=-(" Then myCell.Formula = _
Mid(myCell.Formula, 4, Len(myCell.Formula) - 4)
Next myCell
End If

Application.EnableEvents = True
End Sub



"lob" wrote in message
oups.com...
Is it possible to automatically switch positive values to negative
values to entries in columns v to ir just by entering a 1 in either
column q or r on that same row. Conditional formatting?
Thanks in advance, Lob



  #3   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

<Smack forehead

Of course, my code as written changes negative values to positive, not
positive to negative.

Simply change

If myCell.Value < 0 Then myCell.Formula = _

to

If myCell.Value 0 Then myCell.Formula = _

Sorry about that,
Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Lob,

You would need to use the worksheet's change event. For example, if you
enter a 1 into column Q, the code below will change all negatives to
positives in column V to Z. It also allows you to undo your changes by
changing the 1 back to 0.

Copy the code below, right click on the sheet tab, select "View Code" and
paste the code in the window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range

If Target.Column < 17 Then Exit Sub
Application.EnableEvents = False
If Target.Value = 1 Then
For Each myCell In Range("V" & Target.Row).Resize(1, 5)
If myCell.Value < 0 Then myCell.Formula = _
"=-(" & myCell.Formula & ")"
Next myCell
End If
If Target.Value = 0 Then
For Each myCell In Range("V" & Target.Row).Resize(1, 5)
If Left(myCell.Formula, 3) = "=-(" Then myCell.Formula = _
Mid(myCell.Formula, 4, Len(myCell.Formula) - 4)
Next myCell
End If

Application.EnableEvents = True
End Sub



"lob" wrote in message
oups.com...
Is it possible to automatically switch positive values to negative
values to entries in columns v to ir just by entering a 1 in either
column q or r on that same row. Conditional formatting?
Thanks in advance, Lob





  #4   Report Post  
lob
 
Posts: n/a
Default

Hey Bernie
This works great! Thanks.
I have another spreadsheet that I would like to accomplish a similar
task.
V1=SUM(V23:V200) and this is copied through to IR1.
Is there a code that could be entered to sum only the cells for each
perticular column have a "D" in cell Q for that row,
subtract (create a negative value) to all the cells for each perticular
column have a "P" in cell Q for that row,
and skip (create a neutral value) the sum on the cells for each
perticular column have a "O" in cell Q for that row.
I tried playing with the original code you sent me, but was
unsuccesfull.
Thanks very much for your help, you have opened the door for me to
learn more.
Lob

  #5   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

Lob,

You wouldn't need to use code. For example, the equation

=SUMIF($Q$23:$Q$200,"D",V23:V200)- SUMIF($Q$23:$Q$200,"P",V23:V200)

should add all the values in column V where the corresponding row in column
Q is "D", then subtract any whose corresponding value is "P"

HTH,
Bernie
MS Excel MVP


"lob" wrote in message
oups.com...
Hey Bernie
This works great! Thanks.
I have another spreadsheet that I would like to accomplish a similar
task.
V1=SUM(V23:V200) and this is copied through to IR1.
Is there a code that could be entered to sum only the cells for each
perticular column have a "D" in cell Q for that row,
subtract (create a negative value) to all the cells for each perticular
column have a "P" in cell Q for that row,
and skip (create a neutral value) the sum on the cells for each
perticular column have a "O" in cell Q for that row.
I tried playing with the original code you sent me, but was
unsuccesfull.
Thanks very much for your help, you have opened the door for me to
learn more.
Lob





  #6   Report Post  
lob
 
Posts: n/a
Default

Even Better. This one I understand. Thanks for your help.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Erf (the error function) for both negative and positive numbers Kara Excel Discussion (Misc queries) 0 February 7th 05 02:11 AM
How can I get Positive values only from the random number generat. Markw3700 Excel Discussion (Misc queries) 1 January 21st 05 01:37 AM
Negative numbers turn positive automatically on data entry Jerri Excel Discussion (Misc queries) 4 January 8th 05 06:05 PM
display negative values as a blank cell in Excel pherozeb Excel Discussion (Misc queries) 3 January 5th 05 05:40 AM
display negative values as a blank cell in Excel Pheroze Bharucha Excel Discussion (Misc queries) 0 January 4th 05 11:51 PM


All times are GMT +1. The time now is 11:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"