Change Positive Value in Range of cells to Negative Value based on the another cell Value

[Mohamedazees]

Dear Sir,

I need the VBA to Convert the Positive numbers in rage of cells to Negative Number based on the another Cell Value ( Ref. attached Table)

1. The Conversion required Range are "Value" and "Tax" columns ( I.e. 4th & 5thColumn)
2. The Condition to be matched in column "Note" (I.e. 2nd Column) If the Note value is "Credit note" then the said range value to be converted to negative value

Trade/Legal Name​	Note​	Note number	Value​	Tax	DV	SH	Others
UNITED CONSULTANCY SERVICES INDIA PRIVATE LIMITED	Debit note	CN/2020-21/060	102000​	18360​	0​	0​	0​
WORLDLINE INDIA PRIVATE LIMITED	Debit note	5694029905	249294​	44872.92​	0​	0​	0​
WORLDLINE INDIA PRIVATE LIMITED	Debit note	5694029906	219222​	39459.96​	0​	0​	0​
WORLDLINE INDIA PRIVATE LIMITED	Credit note	5694029907	203612​	36650.16​	0​	0​	0​
WORLDLINE INDIA PRIVATE LIMITED	Credit note	5694031042	11306​	2035.08​	0​	0​	0​
ICICI PRUDENTIAL LIFE INSURANCE COMPANY LIMITED	Credit note	GXL210100001584	1038.56​	186.94​	0​	0​	0​
IFFCO-TOKIO GENERAL INSURANCE COMPANY LTD.	Credit note	H030839100009	3752.37​	675.43​	0​	0​	0​
IFFCO-TOKIO GENERAL INSURANCE COMPANY LTD.	Credit note	H030839100011	2060.58​	370.9​	0​	0​	0​
IFFCO-TOKIO GENERAL INSURANCE COMPANY LTD.	Credit note	H030839100012	4114.97​	740.69​	0​	0​	0​
IFFCO-TOKIO GENERAL INSURANCE COMPANY LTD.	Credit note	4771805100002	58704.88​	10566.88​	0​	0​	0​
IFFCO-TOKIO GENERAL INSURANCE COMPANY LTD.	Credit note	4424159700002	700.7​	126.13​	0​	0​	0​
Total​	​		855806.06​	154045.09​	0.00​	0.00​	0.00​

 

[Peter_SSs]

Try this with a copy of your workbook.

Sub FlipCredits()
  With Range("D2:D" & Range("B" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate("if(" & .Offset(, -2).Address & "=""Credit note"",-1,1)*" & .Address)
    .Offset(, 1).Value = Evaluate("if(" & .Offset(, -2).Address & "=""Credit note"",-1,1)*" & .Offset(, 1).Address)
  End With
End Sub

The totals will seem a bit odd though. Will they need adjusting to reflect the mix of positive and negative numbers above?

 

[jasonb75]

You could probably do it manually in less time than it would take to run a macro.

Filter the data so that only the credit note rows are visible. Type -1 into an empty cell, then copy that cell and pastespecial > values and multiply to the visible value and tax cells.

 

[Mohamedazees]

Thanks Mr.Peter_SSs it works Perfectly

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.