Sub MoveCredits()
With Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row)
.SpecialCells(xlBlanks).FormulaR1C1 = "=-RC[1]"
.Value = .Value
End With
End Sub
Option Explicit
Sub MoveData()
' Select cell B5, *first line of data*.
Range("B3").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
' Insert your code here.
ActiveCell.Offset(0, -1).Value = "-" & ActiveCell.Value
ActiveCell.Value = ""
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Sub MoveCredits_v2()
With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
.Value = Evaluate("if(" & .Address & "="""",-" & .Offset(, 1).Address & "," & .Address & ")")
End With
End Sub
Columns("B").Clear
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | Debit | Credit | ||
2 | 1250000 | |||
3 | 140 | |||
4 | 140 | |||
5 | 535 | |||
6 | 6 | |||
7 | 634 | |||
8 | 2000 | |||
9 | 200 | |||
One col |
Option Explicit
Sub MoveData()
Dim LastLine As Long
Dim i As Integer
LastLine = Range("B" & Rows.Count).End(xlUp).Row
' Select cell B5, *first line of data*.
Range("B3").Select
For i = 3 To LastLine
If ActiveCell.Value = "" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(0, -1).Value = "-" & ActiveCell.Value
ActiveCell.Value = ""
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub