Macro to apply USD inflation rates to values in a dynamic range

[mgm5338]

SO. I'm still pretty new to Excel macros, and found myself needing one a little over my skill level...

I have two sheets within the same workbook, Sheet 1 contains a ton of data, all from the last 10 years. Relevant to this macro are year values, a dollar value, and percentage increase. Sheet 2 contains only two columns, year and USD($) Inflation Rates, Year Over Year.

Essentially what I need to do is use the data in sheet 2 to adjust a dollar value in sheet 1 and apply it to an open column in sheet 1. It's, what I understand to be, a dynamic range, so the percentages are not the same for each year. For example: if the Sheet 1 dollar value is from 2010, I need to aggregate the inflation rates on sheet 2, apply them, and put the 2010 value into 2018 dollars. However, on sheet one, the years are randomized and not chronological. So I need some way of telling Excel to match the years between the sheets, and if the years are equal, run some loop up to 2018 on sheet 2 to adjust to 2018 dollars. It doesn't seem all that complicated, I just do not know how to do it.

If it helps: the sheet 2 values are:

2009 2.70%
2010 1.50%
2011 2.00%
2012 1.70%
2013 1.50%
2014 0.80%
2015 0.70%
2016 2.10%
2017 2.10%
2018 1.90%


Any help you guys can provide would be amazing!

Thanks

Michael

[xladept]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.