VBA change the values to negitive

[MarkCBB]

HI there,

I am looking for some code that changes the values in column E2:E100 to negative, if the name in the corresponding row in column B2:B100 contains the word deduction.

 

[MrKowz]

Assuming all of the values are initially positive, try:

=IF(ISNUMBER(SEARCH("deduction",B2)),(-1)*E2,E2)

 

[MarkCBB]

Hi there,

I am not able to use a helper column due to the size of the database. Need to do this in VBA.

 

[MrKowz]

Try:

Public Sub FixColE()
Dim i   As Long, _
    LR  As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If InStr(Range("B" & i).Value, "deduction") <> 0 Then
        Range("E" & i).Value = (-1) * Range("E" & i).Value
    End If
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[MarkCBB]

Hi there MrKrowz,

Yeah that works raelly fast, thank you.

Kindest regards,
Mark