Does that mean that your data actually looks more like columns A & B below and if you put the formula shown in C2 and copy it across and down, all cells show TRUE?originally all numbers were formatted in Text format like "0.00".
Excel Workbook | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Debit | Credit | ||||
2 | 1250000.00 | 0.00 | TRUE | TRUE | ||
3 | 0.00 | 140.00 | TRUE | TRUE | ||
4 | 0.00 | 140.00 | TRUE | TRUE | ||
5 | 535.00 | 0.00 | TRUE | TRUE | ||
6 | 0.00 | 6.00 | TRUE | TRUE | ||
7 | 0.00 | 634.00 | TRUE | TRUE | ||
8 | 2000.00 | 0.00 | TRUE | TRUE | ||
9 | 0.00 | 200.00 | TRUE | TRUE | ||
One col (4) |
Sub MoveCredits_v3()
With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
.Value = Evaluate(Replace(Replace("if(#="""","""",if(#=0,-^,#))", "#", .Address), "^", .Offset(, 1).Address))
.Offset(, 1).Clear '<- Optional to clear Credit column
End With
End Sub