Results 1 to 5 of 5

Thread: Looking for a Excel Macro to run Bank Reconciliation

  1. #1
    Join Date
    Jan 2012
    Posts
    21

    Looking for a Excel Macro to run Bank Reconciliation

    I need help to write a macro or formulas to do reconcile two sheets. Sheet "A" has list of all the issued cheques for the month and outstanding cheque list from prior months. In Sheet A : column A - Cheque number, Column B - Amount. Sheet "B" has list of cashed cheques for month given by bank. In sheet "B" Column A - Cheque Number and Column B - Amount. I am trying to come up with macro or formula so that cheque numbers can be matched and column C shows the matched cheque number from Sheet A and column D shows the amount, Column E shows the difference in amount i.e. column b-column d. All the matched cheques should get deleted from sheet "A" so that
    sheet A is outstanding list for the cheques.

  2. #2
    Join Date
    Jul 2011
    Posts
    441

    Re: Looking for a Excel Macro to run Bank Reconciliation

    You can do matching cheques and amounts by formula but for deleting them you need macro. My suggestion is don't delete them and leave them for your backup just copy outstanding cheques to another sheet anyways there are another things like deposits and bankfees and also credit cards transaction what do you want to do for them? because you just asked for matching cheques, well I can do it for you just send to me your file my email is

  3. #3
    Join Date
    Jan 2012
    Posts
    23

    Re: Looking for a Excel Macro to run Bank Reconciliation

    We have 4 different bank accounts, One account is for all general transactions like bank fees, credit cards etc. Three Bank accounts are only for cheque clearing. I need to reconcile three cheque clearing bank accounts which is to compare issued cheques and cashed cheques then prepare a outstanding cheques list. I will e-mail you my file soon. Please help me with formula or macro.

  4. #4
    Join Date
    Jun 2011
    Posts
    641

    Re: Looking for a Excel Macro to run Bank Reconciliation

    I had tried using a template which can help to get this work. But when you aer using a template it is not really easy to understand the layout of columns. I can tell you how to create a custom function and shortened. If you explain your problem more accurately (at least for me to understand ) I will make your duties to the letter, because they should not be too complicated. To create your customized function uses macros (alt + F11). It is then possible to assign a shortcut with alt + F8. But if you start (and you have no foundation in programming) I doubt you could do it directly. In my opinion the easiest way is to let me do the programming work (if really necessary). It remains to be you tell me your problem.

  5. #5
    Join Date
    Jul 2011
    Posts
    638

    Re: Looking for a Excel Macro to run Bank Reconciliation

    Here's how to create a shortcut which enables you to moifier the value of your current cell (selected). Run Microsoft Excel and then click on Tools > Macro > Visual Basic Editor.
    1) if you want the shortcut only works in this sheet View > Project Explorer. Double click on the sheet or you want to use your formula. Under this if you want it works for all workbook then click on Insert > Module
    2) You can try to fits the following code:
    Code:
    Sub true () 
    ActiveCell.Value = "true" 
    End Sub
    3) To assign the shortcut back under your workbook click on Tools > Macro > Macro. Select the macro and click options and choose your shortcut. You can also create a function that makes you so if your cell is empty, false if it is "true", and true if it is "false" (or delete) for example. It would have the advantage of not having to use multiple shortcuts.

Similar Threads

  1. Excel 2003 Macro doesn't work in Excel 2007
    By jjaw in forum Windows Software
    Replies: 3
    Last Post: 03-01-2014, 02:28 PM
  2. Replies: 2
    Last Post: 07-02-2012, 04:53 PM
  3. How to use Excel Macro
    By aSITA in forum Windows Software
    Replies: 5
    Last Post: 12-02-2011, 09:34 PM
  4. What is a Macro that is used in MS Excel?
    By Common in forum Windows Software
    Replies: 5
    Last Post: 19-03-2010, 02:08 AM
  5. Record a Macro in MS Excel
    By Soumen in forum Windows Software
    Replies: 2
    Last Post: 23-04-2009, 01:52 PM

Posting Permissions

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