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.
 

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
Ended up expanding the code to include each cell that I needed using the following:

Sub Macro5()
'
' Macro5 Macro
'

'
Range("G7").Select
Selection.Copy
Range("F7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G9").Select
Selection.Copy
Range("F9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G15").Select
Selection.Copy
Range("F15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G17").Select
Selection.Copy
Range("F17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G18").Select
Selection.Copy
Range("F18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G20").Select
Selection.Copy
Range("F20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G21").Select
Selection.Copy
Range("F21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G22").Select
Selection.Copy
Range("F22").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G23").Select
Selection.Copy
Range("F23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G24").Select
Selection.Copy
Range("F24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G25").Select
Selection.Copy
Range("F25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G26").Select
Selection.Copy
Range("F26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G27").Select
Selection.Copy
Range("F27").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G30").Select
Selection.Copy
Range("F30").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H7").Select
End Sub

That updated the cells as I needed them to. Thank you very much for the expert advice.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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