Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change the Values in a Column to 'Minus' If Q
I have 8 columns. My values are in Column 7. These are all 'Positive' values, but the detail in Column D indicates if these values are actually negative or positive (that seems odd, but thats my data layout)
I want to replace values with a minus, if the text "Invoice" or "Debit Note" is in Column D on the same row E.g. Currently Column G shows 8,536.50, but Column D for this Row has "Invoice" detailed, thus I wish to change the value in Column G to -8,536.50 My data I extract from a system, so the length of it may vary depending on when I extract it |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change the Values in a Column to 'Minus' If Q
Hi Sean,
Am Thu, 26 Jan 2017 02:49:16 -0800 (PST) schrieb Sean: I have 8 columns. My values are in Column 7. These are all 'Positive' values, but the detail in Column D indicates if these values are actually negative or positive (that seems odd, but thats my data layout) I want to replace values with a minus, if the text "Invoice" or "Debit Note" is in Column D on the same row E.g. Currently Column G shows 8,536.50, but Column D for this Row has "Invoice" detailed, thus I wish to change the value in Column G to -8,536.50 My data I extract from a system, so the length of it may vary depending on when I extract it try: Sub Test() Dim LRow As Long, i As Long With ActiveSheet LRow = .Cells(.Rows.Count, "G").End(xlUp).Row For i = 2 To LRow Select Case .Cells(i, "D").Value Case "Invoice", "Debit Note" .Cells(i, "G") = -1 * .Cells(i, "G") End Select Next End With End Sub Regards Claus B. -- Windows10 Office 2016 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change the Values in a Column to 'Minus' If Q
Bingo Claus, works perfect, thanks again
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change all cells from minus to plus and plus to minus | Excel Worksheet Functions | |||
Min Values minus 18 months | Excel Worksheet Functions | |||
a sum of #'s minus the lowest two values | Excel Discussion (Misc queries) | |||
CHANGE TRAILING MINUS TO BRACKETS OR PRECEEDING MINUS | Excel Discussion (Misc queries) | |||
... Count, <<< Positive Values minus Negative Values >>> ... | Excel Worksheet Functions |