Forum Discussion

4lvis_bondarenko's avatar
4lvis_bondarenko
Copper Contributor
Mar 05, 2021

Help with formula to convert numbers to negative

Can anyone help with formula to convert numbers in cell to negative ones  for the following scenario: IF cell=''text'' then convert to negative   it also has to be nested with already exsiting for...
Formulas and Functions
4lvis_bondarenko's avatar
4lvis_bondarenko
Copper Contributor
Mar 05, 2021

MyQueries 

Am tryin to make column U convert its value to negative if column S states "S" or if its in red.

Ive tried to add at the end of exsisting formula =IF(S3:S10001="S",*-1   but it didnt seem to work.

JMB17's avatar
JMB17
Bronze Contributor
Mar 06, 2021

4lvis_bondarenko 

 

If you are looking for a vba code, then you could try this. Right click on your worksheet tab, select view code, and paste this code into the code window that appears. Then, save the workbook as an *.xlsm (macro enabled workbook).

 

Private Sub Worksheet_Change(ByVal Target As Range)
     Dim exitReasonRng As Range: Set exitReasonRng = Me.Range("S:S")
     Dim exitPipsRng As Range: Set exitPipsRng = Me.Range("U:U")
     
     
     On Error GoTo ErrHandler
     Application.EnableEvents = False
     
     If Not Intersect(Target.EntireRow, Union(exitReasonRng, exitPipsRng)) Is Nothing Then
          If UCase(Intersect(Target.EntireRow, exitReasonRng).Value) = "S" Then
               With Intersect(Target.EntireRow, exitPipsRng)
                    If IsNumeric(.Value) And Len(.Value) > 0 Then
                         .Value = -Abs(.Value)
                    End If
               End With
          End If
     End If
     
     
ExitProc:
     Application.EnableEvents = True
     Exit Sub
     
ErrHandler:
     MsgBox "Error " & Err.Number & ": " & Err.Description
     Resume ExitProc
     
End Sub

Resources