Interest accross accounts!

[Steve_Courts]

Hi

I'm stuck with creating a macro that can calculate interest correctly regardless of the number of transactions.

I've attached an example which is a simplified version of what I want to do. I'm using Excel 2003.

I have multiple accounts (100+, but I've used 3 in the example), each on a different sheet, and I have monthly rates which will apply across all these accounts.

During the month there are transactions which are being recorded against each account (sometimes there aren't any transactions).

At the end of each month, I want to be able to run a macro on the "Monthly Rates" sheet, which prompts for the month to be used, then, based on that month's interest rate, adds an entry to each account as follows:

Date = Last Day of the Month
Details = "Credit Interest - MMM YY"
CR = Calculation of the monthly interest

I've used formulas to calculate this for Sep and Oct as an example (am pretty sure that a macro would hard-code the interest number, which is fine), but am stuck with how to account for a varying number of transactions across different accounts... doing this manually will take forever each month.

I also need to be able to have a macro that deletes this last interest entry (in case there is a mistake with the rate and I need to re-do it etc.), but this is probably much easier.

To complicate things, the number of accounts can change each month as new ones are added (or inactive ones deleted).

Big ask - is what I want to do possible??

Any help would be greatly appreciated.

Cheers
Steve