• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create a VBA to transfer numbers from one Column to another

Hello,

There I have a Trial Balance with thousands of rows with Debit and Credit Columns. I want to put their numbers into one Column, but Debit should with with + sign and Credits with minus sign.
Column C- Debit, D- Credit.
 

Attachments

Belleke

Active Member
You question is not consistent with your example,please a proper example and state your question clearly.
Thanks.
 

Marc L

Excel Ninja
Hi !
I want to put their numbers into one Column, but Debit should with with + sign and Credits with minus sign.
Obviously according to the explanation and its attachment :​
Code:
Sub Demo1()
    With Sheet1.Range("C7", Sheet1.Cells(Rows.Count, 4).End(xlUp)).Columns
        .Item(1).Value2 = .Parent.Evaluate(.Item(1).Address & "-" & .Item(2).Address)
        .Item(2).ClearContents
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
 

Stevie

Active Member
Hi Dan_K_2014
Marc's solution works perfectly for me.
Please confirm that you inserted a module into your workbook which you attached, pasted Marc's code and ran it.
Stevie
 

Stevie

Active Member
New
HI Stevie,
I copied ad pasted it into a Module, but it does not work...
It seems like an obvious thing to say, but as it is correct code, and works for other people, please can you explain exactly how it doesn't work?
No one can help you unless you do so as no one can work out the problem unless you describe it.
Stevie
 

Doug Jenkins

New Member
Works for me too.

I would have read the two columns as a variant array, looped through the second column to copy negative values to column 1, and delete contents, then written it back to the worksheet.

The method in the macro is very neat, but I never would have come up with it. Is there a good place to find background information on this technique?
 

Stevie

Active Member
The method in the macro is very neat, but I never would have come up with it. Is there a good place to find background information on this technique?
You can look up different functions on MSDN.
Simply put, the macro just puts the value of column c - column d in column c and then clears column d.
Stevie
 

Marc L

Excel Ninja
Is there a good place to find background information on this technique?
Hi Doug and thanks for your revert.

This technique comes from Excel basics
- here an easy beginner level formula like using a helper column -
as respecting the main rule : Think Excel Before VBA !

Once you have the formula you can directly use it within a procedure
via the powerful VBA function Evaluate, to see in VBA inner help …

Another sample in this thread :
Get values from a column & display it in a pre-defined range of columns
 

Doug Jenkins

New Member
Hi Marc, thanks for the reply, and the macro.

I actually use the Evaluate function a lot, and your other example is quite straightforward, but there are a couple of things that were not obvious in this code:
- I didn't know that evaluate works as an array function, so you can use it to add or subtract complete columns in one operation. Knowing that, I will probably use it more.
- It isn't clear why you need the .Parent before evaluate.

By the way, I had a look on MSDN. If there is anything there on using Evaluate in Excel VBA, it is very well hidden.
 
Top