Need To Create A Macro!

I currently have a spreadsheet that I use to record monthly payments, so that I can see how much my new balance is and how many more payments I have left. There are several different items over several rows in the table on the spreadsheet, so when I have made a payment I have to work out each individual item and then change the corresponding column. All the items are at different levels of being paid off and they all have different monthly amounts that need to be paid off, so it can take a while and get quite repetitive. Is there a way that I can record a macro that will allow me to just press a button and the amounts are all reduced and then shown in the correct column automatically?

BomberAF said:

I currently have a spreadsheet that I use to record monthly payments, so that I can see how much my new balance is and how many more payments I have left. There are several different items over several rows in the table on the spreadsheet, so when I have made a payment I have to work out each individual item and then change the corresponding column. All the items are at different levels of being paid off and they all have different monthly amounts that need to be paid off, so it can take a while and get quite repetitive. Is there a way that I can record a macro that will allow me to just press a button and the amounts are all reduced and then shown in the correct column automatically?

Can you post an example of what you are trying to do?

Are the payments the same every month? Are they paid on the same day? If so, it would be fairly trivial to write a macro that would allow you to press a button that would automatically decrement each column by the set monthly payment. Things get a little more involved if the payment is different each month and/or the various payments are made on different days.

If any of these things that are being paid down involve interest, that's another level of complexity.

(I'm extremely jealous of your internet DL speed!)

strollin said:

BomberAF said:

I currently have a spreadsheet that I use to record monthly payments, so that I can see how much my new balance is and how many more payments I have left. There are several different items over several rows in the table on the spreadsheet, so when I have made a payment I have to work out each individual item and then change the corresponding column. All the items are at different levels of being paid off and they all have different monthly amounts that need to be paid off, so it can take a while and get quite repetitive. Is there a way that I can record a macro that will allow me to just press a button and the amounts are all reduced and then shown in the correct column automatically?

Can you post an example of what you are trying to do?

Are the payments the same every month? Are they paid on the same day? If so, it would be fairly trivial to write a macro that would allow you to press a button that would automatically decrement each column by the set monthly payment. Things get a little more involved if the payment is different each month and/or the various payments are made on different days.

If any of these things that are being paid down involve interest, that's another level of complexity.

(I'm extremely jealous of your internet DL speed!)

Thanks for the reply, here is the table that I want to create a macro for:



As you can see there are a number of different payment that all take place on the same day. The payments don't change each month but each item has a different payment.



I am happy with my download speed but my upload is only at 5mbs but shall go to 10 by the end of the year once Virgin Media have finished boosting everyone's speed. I also find it strange the being in California the richest state in the richest country in the world you don't seem to have very fast broadband.

Here's a quick and dirty macro that will do what you want:

Code:

Sub pay_bills()

For x = 4 To 10
   If Worksheets(1).Cells(x, 5) >= 0 Then
      Worksheets(1).Cells(x, 5).Value = (Worksheets(1).Cells(x, 5).Value - Worksheets(1).Cells(x, 4).Value)
      If Worksheets(1).Cells(x, 5) <= 0 Then
         Worksheets(1).Cells(x, 4).Value = 0
      End If
   End If
   
Next

End Sub

You can go into the Visual Basic Editor and Add a module to the first worksheet then copy this code into it and save it. You can then assign a key combo (such as Ctrl-p) to run the macro. After that, every month when you pay your bills just press your assigned key combo to update the values.

strollin said:

Here's a quick and dirty macro that will do what you want:

Code:

Sub pay_bills()

For x = 4 To 10
   If Worksheets(1).Cells(x, 5) >= 0 Then
      Worksheets(1).Cells(x, 5).Value = (Worksheets(1).Cells(x, 5).Value - Worksheets(1).Cells(x, 4).Value)
      If Worksheets(1).Cells(x, 5) <= 0 Then
         Worksheets(1).Cells(x, 4).Value = 0
      End If
   End If
   
Next

End Sub

You can go into the Visual Basic Editor and Add a module to the first worksheet then copy this code into it and save it. You can then assign a key combo (such as Ctrl-p) to run the macro. After that, every month when you pay your bills just press your assigned key combo to update the values.

Thanks for that, I am just about to have my dinner, so when I have finished I will give that a bash.

Cheers

Can you confirm some points for me please.

So the Sub pay_bils(), is the name of the macro you are about to create.

The spreadsheet is on worksheet 3 so I can just use worksheet 3 instead of the name I have used on the spreadsheet.

I don't understand the command 'If Worksheets(1).Cells(x, 5) >= 0 Then'. Do I have to replace x with the corresponding column.

I also don't understand where you get the number 5 from, so can you please explain this a bit more for me if you don't mind.

Sorry for being a pain, but your help is greatly appreciated.

Got this sorted now, working a treat!

Got it working with the help if this gem of a forum http://www.excelforum.com/ I am sure the MODS wont mind this link being posted as it is a great site for EXCEL problems.

Many thanks all I got the solution from this forum but many thanks to all who helped.