VBA: User selects cells and with macro applies compound interest

MyAdventureHat

New Member
Joined
Sep 17, 2014
Messages
4
Hello VBA users way better than me,

I have a workbook with multiple tabs and several lines of budget numbers on each tab.

Each tab is a different project and no two tabs are the same.

Project budgets are arranged by task and Fiscal Year. My goal is to enable the end user of this code to select a random series of budget lines and with the help of a macro apply compounding interest to the selection. In my example below the rate is 1%.

This is my current code:
---------------------------------------
' Keyboard Shortcut: Ctrl+a

Sub Escalate_data()

Dim C As Range

For Each C In Selection
C.Offset(0, 0) = C.Value * 1.01
Next

End Sub
---------------------------------------

In the above, the user can select a random series of data, hit ctrl+a, and have the numbers increase by 1%.

I need help with finding a way for the user to select numbers across multiple years, hit ctrl+a, and have the macro apply an increase of 1.01 to the selection's first column of numbers, (1.01)(1.01) to the second, (1.01)(1.01)(1.01) to the third, and keep increasing for each consecutive column until it hits the end (last column selected in the series).

So if the data looks like the table below, and I randomly select the bolded cells (C2:F3), and press ctrl+a...

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Task A[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Task B[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Task C[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
</TBODY>[/TABLE]


The new series will appear as below:
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Task A[/TD]
[TD]100[/TD]
[TD]101[/TD]
[TD]102.01[/TD]
[TD]103.03[/TD]
[TD]104.06[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Task B[/TD]
[TD]100[/TD]
[TD]101[/TD]
[TD]102.01[/TD]
[TD]103.03[/TD]
[TD]104.06[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Task C[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
</TBODY>[/TABLE]

Thanks in advance for your help and best of luck!

V/R,

Scott
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hi!

Try this code. You can assign your favorite shorcut:

Code:
Sub Esc_by_Columns()
    Dim Col As Range, C As Range
    Dim A&
    
    Application.ScreenUpdating = 0
    For Each Col In Selection.Columns
        A = A + 1
        For Each C In Range(Col.Address)
            C.Value = C * 1.01 ^ A
        Next
    Next
    Set Col = Nothing: Set C = Nothing
    Application.ScreenUpdating = 1
End Sub

Please coment!
I hope it helps! Blessings!
 
Upvote 0

Forum statistics

Threads
1,224,315
Messages
6,177,842
Members
452,809
Latest member
mar_luna

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