Efficient VBA method for looking up cell values - dynamic sheet references and arrays

Hi All

First post here.

I have a workbook that contains price data for various stocks. Each company has it's own sheet (sheet name = Ticker) with columns A:G populated as follows: Dates; Open; High; Low; Close; Adj Close; Volume.

In a front sheet, called "Portfolio Prices" I have a list of company tickers running across B1:Y1. Based on those tickers, I am trying to import Adj Close prices from the various sheets in cells B8:Y#.

All companies have different lengths of price data. I have found a way to copy the dates from the shortest time-series into the "Portfolio Prices" sheet with a macro and helper cell (B5). From there, I want to populate the various columns B8:Y# based on the sheet names in cells B1:Y1 and the dates in A8:A#. I'm not great with VBA, but I found a way to do this using a looped vlookup function over the entire dynamic array. It works, but it is incredibly slow. I'd be lucky to populate 10 cells a second.

Given that the size of the array to calculate might be 100,000+ cells, I need a much faster method. Any ideas?

I've attached the XLSM to this post so you can see what I have been working on.

Thanks
Chris

Try

Please Login or Register  to view this content.

Another approach...

Please Login or Register  to view this content.