Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Need To Create A Macro!


15 Jun 2012   #1

Windows 7
 
 
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?

My System SpecsSystem Spec
.

22 Jun 2012   #2

windows7
 
 

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|
My System SpecsSystem Spec
22 Jun 2012   #3

 

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


22 Jun 2012   #4

Win 7 Ultimate (64-bit), Win 8.1.1 (64-bit)
 
 

Quote   Quote: Originally Posted by BomberAF View Post
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!)
My System SpecsSystem Spec
23 Jun 2012   #5

Windows 7
 
 
Need To Create A Macro!

Quote   Quote: Originally Posted by strollin View Post
Quote   Quote: Originally Posted by BomberAF View Post
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.
My System SpecsSystem Spec
23 Jun 2012   #6

Win 7 Ultimate (64-bit), Win 8.1.1 (64-bit)
 
 

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.
My System SpecsSystem Spec
23 Jun 2012   #7

Windows 7
 
 

Quote   Quote: Originally Posted by strollin View Post
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
My System SpecsSystem Spec
23 Jun 2012   #8

Windows 7
 
 

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.
My System SpecsSystem Spec
23 Jun 2012   #9

Windows 7
 
 

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.
My System SpecsSystem Spec
25 Jun 2012   #10

windows7
 
 

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

Regards,
Rajeev
Dell | Social Media and Outreach|
My System SpecsSystem Spec
Reply

 Need To Create A Macro!




Thread Tools



Similar help and support threads for2: Need To Create A Macro!
Thread Forum
What is Macro / Script ? General Discussion
Macro Notification in Word Microsoft Office
Solved Create a Date macro Word 2010 Microsoft Office
Is there a macro key emulator? Software
Excel Macro Microsoft Office
Excel 2003 Macro Help? Microsoft Office
outlook macro's - how Microsoft Office

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd

All times are GMT -5. The time now is 04:01 AM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33