Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
LATE FEE RECONCILIATION - HELP!!
Hi everyone,
I have a basic account tracking sheet created one per account in a folder called TEST FOLDER. The tracking sheet mainly contain the total charge, amount paid, method of payment, the Month in which the payment is for, date of payment, and balance. Most accounts charge is calculated by Fixed Days X Daily Fee, but some are variable, which is calculated by Variable days X Daily Fee. I would like to set up an automation macro so that it will COLLECT all account who is not paid on time and place all info in a report format excel spreadsheet. I don't think it's practical to have the user goes into each account spreadsheet and label it "LATE" in a particular Cell then run a Macro to collect all the ones that are all labeled. I can't imagine doing this for a large volume, even though I'm not dealing with a large volume right now. Mainly, here is what I think is workable, even though it's still somewhat prone to catch errors. Since each account spreadsheet will be updated by putting the payment information and the Month that it's paid for, let's say Jan payment, if we can filter those account spreadsheets that doesn't have "Jan payment" on them, we can safely assume that those didn't pay yet, and therefore are LATE. First of all, let's call spreadsheet with account summary as <ACCOUNT and the spreadsheet that collect all late paying account as <LATE In <ACCOUNT, here are the cells: [MONTH] - specify month of payment. [VARIABLE DAYS CHECKBOX] - Y/N [DAYS] - if VARIABLE is Y, then there should be amount of Variable days here. [DAILY FEE] - the daily in which is used to multiply with DAYS of the month to come up with the charge. Coding: If [MONTH] in <ACCOUNT doesn't match/exist in <LATE AND [VARIABLE DAYS] is N Then copy [ACCOUNT #] in <ACCOUNT to A2 in <LATE If [ACCOUNT #] doesn't exist Then copy {FILE NAME} of that account to A2 instead (at least file name give some clues, so the A1 won't be blank) End if copy the [DAILY FEE] in <ACCOUNT to B2 in <LATE copy the [DAYS] in A1 in <LATE to C2 in <LATE (row one is the heading row) Else if If [MONTH] in <ACCOUNT doesn't match/exist in <LATE AND [VARIABLE DAYS] is Y Then copy [ACCOUNT #] in <ACCOUNT to A3 in <LATE If [ACCOUNT #] doesn't exist Then copy {FILE NAME} of that account to A3 instead (at least file name give some clues, so the A1 won't be blank) End if copy the [DAILY FEE] in <ACCOUNT to B3 in <LATE copy the [DAYS] in AA1 in <ACCOUNT to C3 in <LATE (because VARIABLE DAYS is Y so DAYS need to be copied from <ACCOUNT instead.) End if Please keep in mind that the copy and paste is "PasteSpecial" only because there will be built-in formula in <LATE to calculate the Late Fee for each account. (maybe it's D2 = B2 x C2) Hopefully the Logic that I have here is correct. I never do any coding in VBA before. Can someone help me code this in Excel VBA please? If there is a better way to Reconcile all of the Late Fee Scenario I mentioned about, I'm gladly open to any suggestion. HEEELPP!!!! Neon520 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reconciliation | Excel Discussion (Misc queries) | |||
Pay Reconciliation | Excel Programming | |||
Late Binding or Late Anything | Excel Programming | |||
Reconciliation | Excel Programming | |||
reconciliation | Excel Programming |