1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'VBA Macros' started by Dan_K_2014, Aug 20, 2018.

  1. Dan_K_2014

    Dan_K_2014 Member

    Messages:
    125
    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.

    Attached Files:

  2. Belleke

    Belleke Active Member

    Messages:
    476
    You question is not consistent with your example,please a proper example and state your question clearly.
    Thanks.
  3. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    Hi !
    Obviously according to the explanation and its attachment :​
    Code (vb):
    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 !
    Chirag R Raval and Doug Jenkins like this.
  4. Dan_K_2014

    Dan_K_2014 Member

    Messages:
    125
    HI Marc L.
    Unfortunately it does not work..
  5. Stevie

    Stevie Active Member

    Messages:
    116
    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
    Marc L likes this.
  6. Dan_K_2014

    Dan_K_2014 Member

    Messages:
    125
    HI Stevie,
    I copied ad pasted it into a Module, but it does not work...
  7. Stevie

    Stevie Active Member

    Messages:
    116
    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
  8. Doug Jenkins

    Doug Jenkins New Member

    Messages:
    13
    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?
    Marc L likes this.
  9. Stevie

    Stevie Active Member

    Messages:
    116
    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
  10. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    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
  11. Doug Jenkins

    Doug Jenkins New Member

    Messages:
    13
    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.
  12. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    .Parent points to Sheet1 in order my procedure still works
    even if Sheet1 is not the active worksheet …
  13. Doug Jenkins

    Doug Jenkins New Member

    Messages:
    13
    Marc L likes this.
  14. Marc L

    Marc L Excel Ninja

    Messages:
    4,257


    Thanks for your link, interesting as always on this blog …​

Share This Page