Make positive value negative if a cell contains

[rsutton1981]

Hi,

Our accounting software exports all values as positive even if it is a credit.

I have tried and failed to make some VBA to do this by it fails

The target is "SC" in Column B from row 15 onwards.
if "SC" is noted then the values in K, L and O should be changed to negative.

using some other VBA code previously used I have got this but it fails

Sub reverse_SC()

' make value negative with MyTarget

Const MyTarget = "SC" ' <-- change to suit

Dim Rng As Range
Dim I As Long, j As Long

' Calc last row number
'j = Cells.SpecialCells(xlCellTypeLastCell).Row 'can be:

j = Range("B" & Rows.Count).End(xlUp).Row

' Collect rows with MyTarget
For I = j To 1 Step -1
If WorksheetFunction.CountIf(Rows(I), MyTarget) > 0 Then
If Rng Is Nothing Then
Set Rng = Rows(I)
Else
Set Rng = Union(Rng, Rows(I))
End If
End If
Next

' Change cell with MyTarget
If Not Rng Is Nothing Then Columns ("k") xValue *-1



' Update UsedRange
  With ActiveSheet.UsedRange: End With

End Sub

Please help

 

[pjmorris]

I assume the accounting software exports a file that you're then copying and pasting into excel. The best way to solve your problem is to import into excel via PowerQuery. It will give you a vastly superior way of managing the import that will work for each and every export automatically (well, okay, you need to remember to refresh the data).

On the 'Data' Ribbon, select 'New Query' on the Get & Transform tab and chose from file. It might take a little learning, but it'll definitely be worth it.

 

[pjmorris]

if you want to use VBA until you've got the hang of PowerQuery (I can't praise it highly enough) then try this:

Sub reverse()
    Dim rCode As Range
    Dim cTarget As Range
    
    Set rCode = Range("B15", Range("B15").End(xlDown))
    
    For Each cTarget In rCode
        If cTarget = "SC" Then
            cTarget.Offset(0, 9) = -cTarget.Offset(0, 9)
            cTarget.Offset(0, 10) = -cTarget.Offset(0, 10)
            cTarget.Offset(0, 13) = -cTarget.Offset(0, 13)
        End If
    Next
End Sub

 

[rsutton1981]

Hi Peter,

had to adjust the range element, it did not seem to work but all worked. it was a much neater bit of code. Thanks

 

[pjmorris]

excellent, glad I was able to help and thanks for the feedback.