Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default Change the Values in a Column to 'Minus' If Q

Bingo Claus, works perfect, thanks again

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
How to change all cells from minus to plus and plus to minus Rockbear Excel Worksheet Functions 4 April 3rd 23 04:28 PM
Min Values minus 18 months Elizabeth Excel Worksheet Functions 13 July 31st 08 08:44 PM
a sum of #'s minus the lowest two values Rynefan Excel Discussion (Misc queries) 5 April 10th 07 12:24 AM
CHANGE TRAILING MINUS TO BRACKETS OR PRECEEDING MINUS Socal Analyst looking for help Excel Discussion (Misc queries) 2 May 12th 06 07:17 PM
... Count, <<< Positive Values minus Negative Values >>> ... Dr. Darrell Excel Worksheet Functions 4 September 8th 05 01:36 PM


All times are GMT +1. The time now is 09:19 PM.

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"