Formula or Macros to put Numbers from one column into One

[Kazdima]

There are 2 columns Debit and Credit both with positive sign.
I need to put numbers from Credit into Debit Column with sign "minus".
[TABLE="width: 152"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Debit[/TD]
[TD]Credit[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1250000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]140[/TD]
[/TR]
</tbody>[/TABLE]

 

[Kazdima]

Hello Logit,
Debits are in Column "C",Credits- in column "D".
Thank you.

 

[Kazdima]

Sorry, Logit, you were asking abut lines. No, Debit and Credits are on different lines and in different columns.

 

[Peter_SSs]

originally all numbers were formatted in Text format like "0.00".

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?

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)

 

[Kazdima]

Hi Peter,

No, it gives "FALSE". I have checked its format, which I am downloading from accounting system, and it is in "Custom" format. But I can cahnge to any format needed.

 

[Peter_SSs]

OK, so the values are numerical and those blank cells were zero values (perhaps formatted to not display). In that case, try this

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

 

[Kazdima]

Thank you, Peter, It is working-
Dan

 

[Peter_SSs]

Thank you, Peter, It is working-
Dan

Glad we got there in the end. Thanks for letting us know.