Excell Calculations

ryankendrick

New Member
Joined
Dec 20, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hello all,
I am trying to figure out how to run a formula in excel. Here is the scenario.
I have a balance, i.e., $300,000 and I make monthly payments of $2,000 each month.
I want to insert a button to where it will show the remainder, but when I click the button again it will update as though a new payment has been made. For example,
Starting balance: $300,000
Payment $2,000
"Click button"
Remainder: $298,000
"Click button"
Remainder: $296,000
"Click button"
Remainder: $294,000

But the catch is that I want the remainder to be calculated in the same cell each time. So when I click the button all the cells or range of cells that need to be updated will change to the new balance. I know that this will probably require a macro or several, but I can't figure this out.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Try this with a simple Macro (I used CTRL-Q instead of a button, but same idea)

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("G6").Select
Selection.Copy
Range("G4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
 
Upvote 0
G6 in the above example would represent the cell with your 300000
G4 in the above example would represent the cell with your calculation result
 
Upvote 0
Try this with a simple Macro (I used CTRL-Q instead of a button, but same idea)

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("G6").Select
Selection.Copy
Range("G4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
I'm going to be up front, I'm a newbie at excel. I can get to Microsoft Visual Basic but once there I am lost. How would I input this into MVB in order for the button to work?
 
Upvote 0
Try practicing with simple Macro's... Go to Developer - Macros ( you may have to turn on the DEVELOPER tab in the Ribbon up top), record Macro. Once you start recording it will capture every click you make and store it in the Macro. In my example for you, I started recording. -- copied your calculation result cell, then pasted VALUE into the original cell, then stopped recording. Under same developer tab you will see Insert --> Form Controls. Pick a button and assign the Macro. :)
 
Upvote 0
Try practicing with simple Macro's... Go to Developer - Macros ( you may have to turn on the DEVELOPER tab in the Ribbon up top), record Macro. Once you start recording it will capture every click you make and store it in the Macro. In my example for you, I started recording. -- copied your calculation result cell, then pasted VALUE into the original cell, then stopped recording. Under same developer tab you will see Insert --> Form Controls. Pick a button and assign the Macro. :)
Thank you for the information but I am unfortunately still lost. I have one cell that has the payment amount, one cell that has the balance and one cell that I want to display the remainder, and once the button is clicked the remainder is updated, being reduced by the payment amount. Sorry for the continual questions, but I really want this to work and I get easily confused. Also this is my first forum where I have been able to ask.
1608490261135.png
 
Upvote 0
Using the image you provided, this will do the job for the information in row 7. You will just need to figure out how to paste this into a Macro then assign it to your button. Good Luck!

Sub Macro5()
'
' Macro5 Macro
'

'
Range("G7").Select
Selection.Copy
Range("F7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
End Sub
 
Upvote 0
Solution
Using the image you provided, this will do the job for the information in row 7. You will just need to figure out how to paste this into a Macro then assign it to your button. Good Luck!

Sub Macro5()
'
' Macro5 Macro
'

'
Range("G7").Select
Selection.Copy
Range("F7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
End Sub
Can this be applied to a range of cells to update all at once?
 
Upvote 0
Thank you for the information but I am unfortunately still lost. I have one cell that has the payment amount, one cell that has the balance and one cell that I want to display the remainder, and once the button is clicked the remainder is updated, being reduced by the payment amount. Sorry for the continual questions, but I really want this to work and I get easily confused. Also this is my first forum where I have been able to ask. View attachment 28307
Using the image you provided, this will do the job for the information in row 7. You will just need to figure out how to paste this into a Macro then assign it to your button. Good Luck!

Sub Macro5()
'
' Macro5 Macro
'

'
Range("G7").Select
Selection.Copy
Range("F7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
End Sub
So that macro didn't work. It only copied and pasted the value. It didn't subtract the payment value from the balance and put the new remainder in the correct cell. Again sorry for the questions
 
Upvote 0
So that macro didn't work. It only copied and pasted the value. It didn't subtract the payment value from the balance and put the new remainder in the correct cell. Again sorry for the questions
My apologies. It did work. I was doing it wrong but how do I apply it to multiple cells all at once?
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top