PDA

View Full Version : [SOLVED] vba code to offset negative values - left or right and make positive



Reeves77
09-02-2016, 05:50 AM
Hi all

I found this vba code solution(thanks esskayb2d) on this site, but looking for a slight modification


Dim rng As Range
Dim lrow As Integer

With ActiveSheet
lrow = .Range("C" & Rows.Count).End(xlUp).Row

For Each rng In .Range("c2:c" & lrow)

If rng.Value < 0 Then

rng.Offset(0, -1).Value = rng.Value
rng.Value = ""
End If

Next rng
End With

End Sub

Scenario:


Debit

Credit




500




-500




250




-150



500




-500




250




-150





Desired outcome



Debit

Credit




500



500





250



150




500





500



250





150




Thanks in advance for any suggestions

Peter

Paul_Hossler
09-02-2016, 07:13 AM
Maybe this





Option Explicit
Sub test()
Dim rng As Range
Dim lrow As Long

With ActiveSheet
lrow = .Range("C" & .Rows.Count).End(xlUp).Row

For Each rng In .Range("c2:c" & lrow)
If rng.Value < 0 Then
rng.Offset(0, 1).Value = -rng.Value
rng.ClearContents
End If

Next rng

lrow = .Range("D" & .Rows.Count).End(xlUp).Row

For Each rng In .Range("d2:d" & lrow)
If rng.Value < 0 Then
rng.Offset(0, -1).Value = -rng.Value
rng.ClearContents
End If

Next rng


End With

End Sub

Reeves77
09-02-2016, 07:22 AM
That works a treat - awesome, thank you very much Paul.