VBA: User selects cells and with macro applies compound interest

[MyAdventureHat]

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

 

[johnmpl]

Hi!

Try this code. You can assign your favorite shorcut:

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!

 

[MyAdventureHat]

Johnmpl that is some righteous coding! Thanks for the help. It works like a charm.