Formula or Macros to put Numbers from one column into One

Kazdima

Board Regular
Joined
Oct 15, 2010
Messages
226
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]
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
If the 'blank' cells are actually blank, try this on a copy of your data.
Code:
Sub MoveCredits()
  With Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=-RC[1]"
    .Value = .Value
  End With
End Sub
 
Upvote 0
.
Another method :

Code:
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
 
Upvote 0
.. and another one
Code:
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
Code:
Columns("B").Clear
@Logit
Yours will only do the first section if data appears like this


Book1
AB
1DebitCredit
21250000
3140
4140
5535
66
7634
82000
9200
One col
 
Upvote 0
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.
 
Upvote 0
.
Here is an edit to my post #3 :

Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
.
Re Post #7 ...

You have a Debit and a Credit on the same line ?
 
Upvote 0
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.
[TABLE="width: 79"]
<colgroup><col width="79"></colgroup><tbody>[TR]
[TD="class: xl63, width: 79"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top