check for negative and change the value in next column

[GirishDhruva]

Hi every one ,

i am trying to search for negative value in column A and if negative value is found

then the value in next column(i.e, Column C ) should be checked and replaced if the value is 40 then to 50 or if the value is 50 then to 40 .

And even i need to change the negative value to positive and can we round off the decimal value .

Below is the sample workbook.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Regards,
Dhruva.

 

[DanteAmor]

Here is a completely different way to write the macro...

Hi Rick, it is certainly another different way to write the macro, but it does not solve the user's requirement to round the number.

And even i need to change the negative value to positive and can we round off the decimal value .

In addition, your macro replaces the "-" in all sheets.

-----------------------------------------------------------------

Here my code compacted slightly:

Sub check_negative()
  With Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate("=IF({1},IF(" & .Offset(, 4).Address & "<0,50," & .Address & "))")
    .Offset(, 4).Value = Evaluate("=IF({1},ABS(ROUND(" & .Offset(, 4).Address & ",0)))")
  End With
End Sub

 

[DanteAmor]

Thank you @DanteAmor and @igold ,both solution works perfectly.

I'm glad to help you. Thanks for the feedback.