Windows 7: Need To Create A Macro!

BomberAF · 15 Jun 2012

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?

Dell Rajeev A · 22 Jun 2012

Hi BomberAF,
My name is Rajeev and I work for Social Media and Community at Dell,
Not to worry we will get this issue resolved for you,
i'm mentioning couple of links below, which you can follow to create Macros on a spreadsheet,

Redirect Notice

Record and use Excel macros - Excel - Office.com

Excel 2007 / 2010 Macro Recorder Tutorial

How to Create a Simple Macro in Excel

regards
Rajeev
Dell | Social Media and Outreach|

z3r010 · 22 Jun 2012

Please dont post shortened urls on the forum, it makes it much harder to spot malicious links.

strollin · 22 Jun 2012

Quote: Originally Posted by BomberAF

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!)

BomberAF · 23 Jun 2012

Quote: Originally Posted by strollin

Quote: Originally Posted by BomberAF

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.

strollin · 23 Jun 2012

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.

BomberAF · 23 Jun 2012

Quote: Originally Posted by strollin

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

BomberAF · 23 Jun 2012

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.

BomberAF · 23 Jun 2012

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.

Dell Rajeev A · 25 Jun 2012

Hi BomberAF,
Glad to hear that you got it working,

Regards,
Rajeev
Dell | Social Media and Outreach|

Windows 7: Need To Create A Macro!

Need To Create A Macro! problems?