Real challenge from my professor

[wenyuanalive]

Hey guys,

My professor gave me an assignment, where I have no clue.

Say, in an excel spreadsheet, if you input "SP201203"(meaning S&P 500 information on March, 2012) into cell A1, the range of A2:F501 will output all S&P 500 companies information at March 2012, coming from a local xls file which contains those information.

If you change cell A1 into "SP201204", all the current information from range A2:F501 will be saved into a csv file named "201203" in your local drive (for later use in MATLAB) and the information of S&P 500 companies at April 2012 from a local xls file will replace the previous information on the range of A2:F501.

In a word, whatever you put in cell A1, A2:F501 will return corresponding information extracted from a local file and save the previous information as a csv file named after the date of the information.

Any tips on how to do this using VBA?

Thanks.

[mikerickson]

It sounds like the saving to CSV should be driven by a Change event.

Accomidation should be made for if after changing from "SP201203" to "SP201204", the user changes back to "SP201203" to avoid duplication of the csv file.

I would start with filling the cells A2:F501 with formulas to pull the data from the local xls file. Some combination of VLOOKUP and INDIRECT perhaps.

[wenyuanalive]

thanks! That is a cell change event!