Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Looking for a Excel Macro to run Bank Reconciliation

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 21-01-2012
Member
 
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.

Reply With Quote
  #2  
Old 21-01-2012
Member
 
Join Date: Jul 2011
Posts: 428
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
Reply With Quote
  #3  
Old 21-01-2012
Member
 
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.
Reply With Quote
  #4  
Old 21-01-2012
Member
 
Join Date: Jun 2011
Posts: 632
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.
Reply With Quote
  #5  
Old 21-01-2012
Member
 
Join Date: Jul 2011
Posts: 630
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.
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Looking for a Excel Macro to run Bank Reconciliation"
Thread Thread Starter Forum Replies Last Post
Excel 2003 Macro doesn't work in Excel 2007 jjaw Windows Software 3 03-01-2014 02:28 PM
Need help to find Bank Statement Reconciliation template for Excel ELETTRA| MS Office Support 2 07-02-2012 04:53 PM
How to use Excel Macro aSITA Windows Software 5 12-02-2011 09:34 PM
What is a Macro that is used in MS Excel? Common Windows Software 5 19-03-2010 02:08 AM
Record a Macro in MS Excel Soumen Windows Software 2 23-04-2009 01:52 PM


All times are GMT +5.5. The time now is 12:28 AM.