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".

Debit	Credit
1250000
	140
	140
	140
	140

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Peter_SSs]

If the 'blank' cells are actually blank, try this on a copy of your data.

Sub MoveCredits()
  With Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=-RC[1]"
    .Value = .Value
  End With
End Sub

 

[Logit]

.
Another method :

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

 

[Peter_SSs]

.. and another one

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

For either of my codes if you want to remove the old credit columns just add as the 2nd last line of code

Columns("B").Clear

@Logit
Yours will only do the first section if data appears like this

Book1
	A	B
1	Debit	Credit
2	1250000
3		140
4		140
5	535
6		6
7		634
8	2000
9		200
One col

 

[Kazdima]

Hello Peter,
for some reason both variants are not working...
My Debit heading is in Cell C6, Credit heading is in D6.
Cells without numbers contain "0.00".
Thank you.

 

[Logit]

.
Here is an edit to my post #3 :

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

 

[Kazdima]

Thank you, Logit.

Your Macros is working, but it missing One point.
It gives "minus" sign to Credits and transfers them into Debit Column, BUT at the same time it deletes existing numbers in Debit Column.

So, 1, Numbers in Column Debits should stay in addition to transferring data from Credit column.

Thank you

 

[Peter_SSs]

Cells without numbers contain "0.00"

Was a bit hard to guess that from your sample!

Is that text "0.00" or just the number 0 formatted to 2 decimal places?

 

[Logit]

.
Re Post #7 ...

You have a Debit and a Credit on the same line ?

 

[Kazdima]

Hello Peter,

originally all numbers were formatted in Text format like "0.00". I can live them as they are or format into General. Advise what should I do, and does it matter where I put my cursor before running a MAcro.
Thank you.

<colgroup><col width="79"></colgroup><tbody>
</tbody>