Morning all, hope you're all doing well. New member here
I'll dive straight to the point....
started an Excel unit in an IT/Admin course and having previously sailed through the basic Excel formula and functions (stuff
like :IF, SUM, Concatenate, conditional formatting, FV, Subtotals), I've got myself stuck trying to create a nifty tool using
Macros in VBA.
I've produced an "Investment Table" using the FV function to allow the user to input
things like interest rates, payments, number of payments, years of investment etc, and using these values the FV function
throws out the resulting value (future value). Now, on a separate page I'm trying to "break down" this tool into
numerous year-by-year tables showing how the value of the user's investment changes year-by-year and payment-by-payment.
For example, Mr Smith invests £100, 4 times per year, for 5 years, with an interest rate of 3.5%. Currently, all he
can see is what his value will be worth at the end of the 5 year investment. But using the year-by-year tables he'd see a
separate table for each year, and so he can see that in Year 2, Payment 3 his investment will be worth
Here's where the macro comes in; in the main "Investment Table" the user inputs the
"Years of Investment" (Cell D2). Using this (number) value, and on a separate sheet, a macro needs to
produce/replicate the year-by-year table based on the value in cell D2. The aim of this is to save the user having to C&P
the table or create their own, X-amount of times. The macro is needed because the tool must be flexible to account for
different lengths of investment - and so obviously it's not practical to have to C&P if a user is investing for, say, 25
Put simply, "Table X" must be copy & pasted Y amount of times, where Y = D2
I've tried to find somewhere to start in VBA, having done basic macros in it before, but I'm lost
Hope this makes
sense but I will of course clarify if needed.