create negative value in one cell based on value in another

[rickf19]

Hi

I have a list of headings in col A however in col I all the amounts are positive I need to change the value in col I to a negative if the heading in col A is Fees, there are 40 or so different headings over hundreds of rows I have filtered col a to show only Fees but cant change the positive to negative in col I without affecting all the other headings once I take the filter off

Any ideas gratefully accepted

Rick

 

[Joe4]

Try this:

Sub NegateFees()

    Dim r As Long
    Dim lr As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column I with data
    lr = Cells(Rows.Count, "I").End(xlUp).Row
    
'   Loop through all rows of data
    For r = 1 To lr
'       See if column A equals "Fees"
        If Cells(r, "A") = "Fees" Then
'           Negate value in column I
            Cells(r, "I").Value = 0 - Cells(r, "I").Value
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[rickf19]

Hi Joe
Took me a while but managed to create the macro and run it, seems to have done the trick.

Many thanks
Rick

 

[Joe4]

You are welcome.
Glad I was able to help!