+ Reply to Thread
Results 1 to 38 of 38

Bank Reconciliation with VBA

  1. #1
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Bank Reconciliation with VBA

    Xl genius I need your help!!!!!!
    I have the attached xl file and I would like to make a reconciliation through all this transactions.
    I would like to compare first, all “N” the “REF” column. If the find the same (one or more times) I would like to check the “M” column and make the total sum (of this reference) in order to “much” and make zero total.
    As an example [ATTACH]378738
    -10 REF2525
    9 REF 2525
    1 REF 2525
    I want all the above transactions to exclude or to highlight this transactions.
    After all this I would like to have all this transactions without munching in ref column and without the total sum of this sums zero

    Thank a lot in advanced
    Attached Files Attached Files
    Last edited by chrism_cfu; 02-21-2015 at 03:17 AM. Reason: New topic title

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,118

    Re: Reconsiliation

    deleted....
    Last edited by protonLeah; 02-21-2015 at 12:09 AM.
    Ben Van Johnson

  3. #3
    Forum Guru 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,747

    Re: Reconsiliation

    @protonLeah

    I attempted to PM you on this Thread Title but it told me

    protonLeah has chosen not to receive private messages or may not be allowed to receive private messages. Therefore you may not send your message to him/her.
    I'd suggest MANY of us know pretty much what the OP is looking for by just looking at the Title. Sure, perhaps it's not as expansive as it should be...perhaps it should have said
    Bank Reconciliation with VBA
    but that title would not have told us anything more significant.

    I think, should I wish to spend time on this Thread (it's not easy), I may choose to not honor this admonishment
    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Sorry...
    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.

  4. #4
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Reconsiliation

    i m sorry to bridge the rule 1
    Last edited by chrism_cfu; 02-22-2015 at 03:13 AM.

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,118

    Re: Reconsiliation

    google:

    About 50,500,000 results
    Showing results for Reconciliation
    Last edited by protonLeah; 02-21-2015 at 12:21 AM.

  6. #6
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Reconsiliation

    i dont want to "play" with words like ie, matching columns : 62.100.000...
    i prefered in order to do all this conversation regarding "rules" to give me the right path/suggaestion if you wish to help me ...
    As my friend above suggested a new title, i ll change it to be harmonized with the forum rules...

    thanks a lot once again
    Last edited by chrism_cfu; 02-21-2015 at 03:19 AM.

  7. #7
    Forum Guru 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,747

    Re: Reconsiliation

    Hi chrism_cfu

    I've not forgotten about you. I'm looking at it.

    Please explain this a bit further...to what are you referring?
    i dont want to "play" with words like ie, matching columns : 62.100.000...

  8. #8
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Reconsiliation

    Quote Originally Posted by jaslake View Post
    Hi chrism_cfu

    I've not forgotten about you. I'm looking at it.

    Please explain this a bit further...to what are you referring?
    It just a phrase nothing to worry about. thanks jaslake

  9. #9
    Forum Guru 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,747

    Re: Reconsiliation

    Hi chrism_cfu

    Place both of these workbooks in the SAME Folder

    Open the Code Book, Click the Button. You'll be prompted to select your Import File. The Code will do the rest.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Bank Reconciliation with VBA

    Jaslake works perfect! But! i missed to add one more parameter.
    because of currencies discrepancies i would like to add one more step. if the the ref column is the same and the l column of this two same ref has d (from depit) and c (from credit) or has different currency in k column ie depit usd and credit euro (maybe the second parameter are better) in this case the enrollment must closed.
    k l m ref
    ie usd d -20.000 sxc1212
    eur c 18.500 sxc1212

    the above enrollment must closed even the total amount is different when we make the sum

    thanks a lot for your help and really sorry for this last minute addition

  11. #11
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    Hi chrism_cfu

    Let me look at it for a bit...

  12. #12
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    Hi chrism_cfu

    As before, place both of these workbooks in the SAME Folder

    Open the Code Book, Click the Button. You'll be prompted to select your Import File. The Code will do the rest.

    Please check the results CAREFULLY...only you know what they SHOULD be.

    The Code takes about 30 seconds to run on my Platform...be patient.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Bank Reconciliation with VBA

    jaslake i believe it works P e r f e c t !!!!
    i make some more tests on Tuesday (today is like bank holiday in Greece) with a real data. but i am totally sure that the vba that you create works perfect.
    what can i say for your help!! a big thanks is not enough !!
    the more i can do for you is a free guiding tour when you visit Greece

  14. #14
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    Hi chrism_cfu

    You're welcome...hope testing goes well...let me know.

    This most likely will not happen...I'm not a Traveler but I appreciate the offer.
    the more i can do for you is a free guiding tour when you visit Greece

  15. #15
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Bank Reconciliation with VBA

    Dear jaslake

    For the first I would like to roll back to the first scenario (the vba that you made for the first time). this is what finale want to do...
    When I run (I attached new sample) they much correctly the enrollments but close also the yellow highlighting rows.
    This rows must be open because they are single. So they must be open.

    Could you have be so kind to take another look to this one?

    Thanks a lot for your help

    import2.xlsx

  16. #16
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    Hi chrism_cfu

    To revert to the Original Code, in Code Book, in Sub Reconcile(), comment out the Line of Code as indicated..
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Bank Reconciliation with VBA

    sorry i cant follow
    maybe my bad english didnt help ...
    as a recap i would like to say that i didnt want the vba to close the enrollments that had different currency different amount but the same reference. i want to closed only the enrollments with same reference and the tolal amount was zero. as the first vba that you create.
    my worries are that when i "run" the macro they closed also the yellow enrollments (see attached file im my previews post) that the dont have the criteria that i mention previously. could you please check why they closed this enrollments? (i make the changes that you said above but still the yellow enrollments disappear)

    thanks a lot
    Last edited by chrism_cfu; 02-25-2015 at 02:32 AM.

  18. #18
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    Hi chrism_cfu

    The attached File is the same File as is attached to Post #9.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Bank Reconciliation with VBA

    did you check why the yellow enrolments "closed" when i run the "code" ?because this rows are unique in import file.

    thanks a lot for your patience

  20. #20
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    Hi chrism_cfu

    Attached is the Output I get as a result of running the Code. Am I missing something>
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Bank Reconciliation with VBA

    Jaslake after thousands ( ) of tests, finale im in a position to say that the vba works perfect!!!
    thanks a lot for your help and your patience!!!!

    thank you a million

  22. #22
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    You're welcome...glad I could help. Thanks for the Rep.

  23. #23
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Bank Reconciliation with VBA

    after great work from Jaslake im coming to ask one more add on to existing xl file.
    i would like to delete the rows when the reference is the same the sum of the amount is zero (when the rows has one credit and one depit letter in l column) and the currency is the same.

    as example in row 607 there are 8 rows with the same reference but with the different currency eur - usd. i would like to delete the usd rows (same reference 4 lines with 2 "d" and two "c" in line L and same currency. but i would like to remain from this references sequense the eur rows because een has the same reference and currency but the have not the zero amount in column "M"
    2.3.xlsx

  24. #24
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    Hi Chris

    Please attach the Code Book you're currently using.

  25. #25
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Bank Reconciliation with VBA

    im using this one

    thanks
    Attached Files Attached Files

  26. #26
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    Hi Chris

    It'll take me a bit to work out the logic...won't be tonight. It may help if you posted your raw 2.3.xlsx File before ANY Code has been applied.

  27. #27
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    Hi Chris

    Try the revised Code in Code Book attached.
    Attached Files Attached Files

  28. #28
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Bank Reconciliation with VBA

    jaslake thanks for your help ...
    when i run the code the 2.3.xslx file crash and appear the attached screen
    Attached Images Attached Images

  29. #29
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    Hi Chris

    Please attach a jpg image...I'm unable to view png images.

  30. #30
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Bank Reconciliation with VBA

    i hope you can see the image now
    Attached Images Attached Images

  31. #31
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    Hi Chris

    Yes, I can see it now. Was the Code run on the Sample File (2.3.slsx) or a different File?

    I have to pick up my Grandson at the Buss stop in a few minutes so I'll need to get back to you later.

  32. #32
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Bank Reconciliation with VBA

    i have this issue both to sample file and to a different file.

    sure no worries ... n joy your time with your Grandson and find the solution when you have time

  33. #33
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    Hi Chris

    You'll get that error if no records are found.

    Add the Lines of Code as indicated
    Please Login or Register  to view this content.

  34. #34
    Registered User
    Join Date
    11-28-2012
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Bank Reconciliation with VBA

    hi John!
    i made many test regarding the new file and one more time works perfect!!
    many many many thanks for your time and for your kindness to help me...

  35. #35
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    You're welcome...glad I could help.

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

  36. #36
    Registered User
    Join Date
    04-29-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Bank Reconciliation with VBA

    John,

    can you look at another scenario with the same data, for example? if we have data to reconcile from accounting in column "M" and from the bank statement in Column "O").
    And do not delete reconciled items, but to copy "reconciled" and non-reconciled" into two new sheets?
    Thank you,

    Dan

  37. #37
    Forum Guru 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,747

    Re: Bank Reconciliation with VBA

    Hi Dan

    Have you started a Thread for your issue (Forum Rules)? Please post a Link to this Thread in your new Thread.
    If you wish, PM to me a Link to your new Thread...I'll be happy to look at it with you.

  38. #38
    Registered User
    Join Date
    04-29-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Bank Reconciliation with VBA

    Thank you, John. I will do.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Putting a border around a column of text and weekly reconsiliation
    By Ranny30 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-14-2005, 03:57 PM

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