+ Reply to Thread
Results 1 to 17 of 17

Create two line journal entry out of one line

  1. #1
    Registered User
    Join Date
    11-30-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    10

    Create two line journal entry out of one line

    I'm trying to write a macro that will allow me to copy existing data into a slightly different format. Basically, I would like to take a one line journal entry and break it into two lines and add some data to those two lines. I've attached a file which shows my data as is and the result I'd like. Any help provided would be greatly appreciated!
    Attached Files Attached Files
    Last edited by redwine; 12-17-2010 at 12:55 PM.

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Create two line journal entry out of one line

    Hi redwine

    Do you want the results on the same sheet, a new sheet or doesn't it matter?

    John
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    11-30-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Create two line journal entry out of one line

    A new sheet would be perfect.

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Create two line journal entry out of one line

    Hi redwine

    Does your original data look like the attached? If not, please post a file of your original data.

    John
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-30-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Create two line journal entry out of one line

    yes, that's the original data.

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Create two line journal entry out of one line

    Hi redwine

    Try this
    Please Login or Register  to view this content.
    John
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-30-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Create two line journal entry out of one line

    Hi John,

    Thanks so much! This worked flawlessly on the test data, but I had one small issue on my actual data (about 200 lines). I found that the account (Const Account = 43000) only copied to lines 2-9. The debit/credit (40/50) did copy to all the lines. I tried to play around with the macro to get the account to copy down, but I'm not having any luck. Any ideas/thoughts?

    Thanks again for your time - this is a huge times savings for me!

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Create two line journal entry out of one line

    Hi redwine

    Ha...well it's doing what it's TOLD to do. I'll post the corrected line of code in a few minutes.

    John

  9. #9
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Create two line journal entry out of one line

    Hi redwine

    Change this line of code from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    That should resolve the issue.

    John

  10. #10
    Registered User
    Join Date
    11-30-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Create two line journal entry out of one line

    That did it! Thanks so much, John!

  11. #11
    Registered User
    Join Date
    11-30-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Create two line journal entry out of one line

    I might have spoken to soon...

    I see one other issue: if the value in column C (DR CC) contains the same data in two consecutive rows, the DR/CR value populates the CC rather than the 40/50. Other than that, it's still totally flawless and quick.

  12. #12
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Create two line journal entry out of one line

    Hi redwine

    Can you post a file of data that exhibits the issue?

    John

  13. #13
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Create two line journal entry out of one line

    Hi redwine

    Never mind...don't post a file. I've been able to duplicate the issue. I'll get back to you.

    John

  14. #14
    Registered User
    Join Date
    11-30-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Create two line journal entry out of one line

    Quote Originally Posted by jaslake View Post
    Hi redwine

    Can you post a file of data that exhibits the issue?

    John
    Hi John - here you go. Can't thank you enough for all your help!
    Attached Files Attached Files

  15. #15
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Create two line journal entry out of one line

    Hi redwine

    Gotta warn you to not quote entire posts when you respond. The bosses don't like it.

    In this code, I've commented out two lines of code. Don't know why they were in there to begin with
    Please Login or Register  to view this content.
    Make the same changes to your code and the issue should be resolved.

    John

  16. #16
    Registered User
    Join Date
    11-30-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Create two line journal entry out of one line

    Hi John,

    Thanks for the heads up about quoting - won't happen again.

    Removing the two lines of code did the trick. I've tested it on my full set of data and it worked perfectly. Thanks, John, you're a rockstar!

  17. #17
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Create two line journal entry out of one line

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1