VBA codes to convert number value in column D to negative based on corresponding Text in column B

[Sean15]

Can someone post VBA codes to convert number value in column D to negative if corresponding text in column B contains the word Credit or Payment.

Book1
	A	B	C	D	E
1	Date	Description	Class	Cost	Cost to date
2	07/01/23	Charge - RUBS	Debra	24.49	(2,285.85)
3	07/01/23	Credit - Valet Trash -	Debra	(9.07)	(2,310.34)
4	06/02/23	Payment - eCheck	Mike	(1,117.97)	(2,301.27)
5	06/01/23	Credit - RUBS - Electric -	Debra	(70.09)	(1,183.30)
6	06/01/23	Charge - RUBS - Electric -	Debra	32.57	(1,113.21)
7	06/01/23	Credit - Valet	Debra	(25.58)	(1,145.78)
8	05/02/23	Payment - eCheck	Mike	(1,132.58)	(1,120.20)
9	05/01/23	Charge - RUBS	Debra	12.38	12.38
Sheet1

Cell Formulas
Range	Formula
E2:E9	E2	=E3+D2

Thank you.

 

[MARK858]

A bit puzzled by this as your numbers in red are already negative (they are just formatted to be red and in brackets)

 

[Sean15]

Sorry for the confusion. I changed values in column D myself to show the expected results. I want to automate because the worksheet can get quite large.

 

[Rick Rothstein]

Maybe this macro...

Sub MakeCreditsPaymentsNegative()
  Dim R As Long
  For R = 2 To Cells(Rows.Count, "D").End(xlUp).Row
    If Left(Cells(R, "B").Value, 6) = "Credit" Or Left(Cells(R, "B").Value, 7) = "Payment" Then
      Cells(R, "D").Value = -Cells(R, "D").Value
    End If
  Next
End Sub

 

[MARK858]

@Rick Rothstein

if corresponding text in column B contains the word Credit or Payment

 

[Rick Rothstein]

@Rick Rothstein

Argh! I missed that! I just changed the code in my original post to fix that oversight. Thanks for catching that for me.

 

[Peter_SSs]

.. or all at once?

Sub Neg()
  With Range("D2", Range("D" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(isnumber(search(""credit"",#))+isnumber(search(""Payment"",#)),-1,1)*" & .Address, "#", .Offset(, -2).Address))
  End With
End Sub

 

[Sean15]

I like VBA code in post #7.

Code works wells on small sample data. I noticed if code is run multiple times, value in column D changes each time. Since new data is added to worksheet each month, code will be executed each time data is added. My fault: I should have thought of it and presented it in post #1.

Could you adjust code to allow for: if value in column D is already negative (based on criteria), no change in value. Again, my apologies.

 

[Peter_SSs]

Could you adjust code to allow for: if value in column D is already negative (based on criteria), no change in value.

Try

Sub Neg_v2()
  With Range("D2", Range("D" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(isnumber(search(""credit"",#))+isnumber(search(""Payment"",#)),-1,1)*abs(" & .Address & ")", "#", .Offset(, -2).Address))
  End With
End Sub

 

[Sean15]

Thank you very much.