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