Macro to calculate monies lent out

[Zyphon]

Hi Guys,

I have a spreadsheet which is attached, I wanted to be able to track money that I have lent to various people.

What I probably need is a little macro that I can attach to the SUBMIT button I have made and every time someone pays some of what they owe. it would automatically subtract the amount paid in column D to the Amount Remaining in column C and then column D would revert back to blank. Now the ranges to column C and D are rows 4 to 200.

So that every time someone paid back some of their monies owed I would put this in D until such time as the amount due was 0.

I can't think of a way to do this by formula and would think VBA would be the best way to do it as any value put in column D would be reset if I cleared column D.

Does this make sense?

Can anyone help me please?

Thanks

[royUK]

If you clear the cell each time then yo have no record of what has been paid & when.

[Zyphon]

If you clear the cell each time then yo have no record of what has been paid & when.

Yeah I was kind of hoping for a macro that would subtract what is paid in column D and subtract it from column B so there is always a running tally in column C, I was thinking of storing the remaining amount in a variable or registry in VBA.

Can this not be done?

[royUK]

I think this is better, I have used SUBTOTAL & AutoFilter

[Zyphon]

I think this is better, I have used SUBTOTAL & AutoFilter

Thanks for that royUK, what I started thinking about over the weekend was using another sheet as a database and maybe using a UserForm that would populate this.

For example in my workbook on Sheet1; The first name starts at A4. Now on Sheet2 we could put this name on A1 andt then all his payments in A2 all the way down to A whatever. Similarly the name on Sheet1 cell A5 could go on Sheet2 on B1 with the payments in B2 and down and so on and so forth.

Maybe this approch would be better? A least this way you could track payments.