Excell Calculations

ryankendrick

New Member
Joined
Dec 20, 2020
Messages
7
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.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

starkc69

New Member
Joined
Dec 20, 2020
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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
 

starkc69

New Member
Joined
Dec 20, 2020
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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
 

ryankendrick

New Member
Joined
Dec 20, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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?
 

starkc69

New Member
Joined
Dec 20, 2020
Messages
14
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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. :)
 

ryankendrick

New Member
Joined
Dec 20, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 

starkc69

New Member
Joined
Dec 20, 2020
Messages
14
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 
Solution

ryankendrick

New Member
Joined
Dec 20, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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?
 

ryankendrick

New Member
Joined
Dec 20, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 

ryankendrick

New Member
Joined
Dec 20, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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
Top