Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change dollar amount to a negative if another colum has a "P"
I have numbers in a colum that I want to change to a negative(PAID) amount if
there is a "P" in the next column. I need to be able to enter numbers so I cannot place a formula in the numbers column. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change dollar amount to a negative if another colum has a "P"
Marvin
Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then N = Target.Row If Excel.Range("A" & N).Value < "" _ And Excel.Range("B" & N).Value = "P" Then Excel.Range("A" & N).Value = Excel.Range("A" & N).Value * -1 End If End If enditall: Application.EnableEvents = True End Sub Event Code entered into the sheet module. Will change the numbers to negative as you enter them in column A if column B has a "P". Right-click on sheet tab and "View Code". Paste into that module. Gord Dibben Excel MVP On Mon, 26 Sep 2005 16:49:05 -0700, Marvin wrote: I have numbers in a colum that I want to change to a negative(PAID) amount if there is a "P" in the next column. I need to be able to enter numbers so I cannot place a formula in the numbers column. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change dollar amount to a negative if another colum has a "P"
you might try the following and then make adjustments as needed:
If ALL of the numbers are currently +, the following will find the "P's" and change the numbers to neg. Warning: if you run this again it will again reverse the signs. Adjust accordingly. Sub FindPmts() Dim Amts As Range Dim oCell As Range Set Amts = Range("A1:A100") For Each oCell In Amts If UCase(oCell.Offset(0, 1)) = "P" Then oCell = oCell * -1 End If Next End Sub "Marvin" wrote: I have numbers in a colum that I want to change to a negative(PAID) amount if there is a "P" in the next column. I need to be able to enter numbers so I cannot place a formula in the numbers column. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
change the colour of the "invert if negative" option in Excel | Charts and Charting in Excel | |||
Sum dollar amount if it has an "S" next to it. | Excel Discussion (Misc queries) | |||
How do I change "." to "," so e.g. 1.000 = 1,000 in the same colum | Excel Discussion (Misc queries) | |||
How do I enter a negative dollar amount? | Excel Discussion (Misc queries) |