VBA - Advanced lookup and nested if's for a statistical analysis

[andyma80]

I'm attempting to create a macro that will lookup the value in a cell, run a separate macro (statistical analysis), copy a range, then paste those values in another worksheet.

Per the attached sample workbook:

I want to run a macro, "Analysis" which I have already created, for each entry, starting at A4 under the 'Data' worksheet (These entries are serial numbers for a specific engine). This data will change depending on what I want to filter it by and can range from 10 entries to 400.

The Analysis macro is dependent on the A1 cell in the 'Forecast' worksheet. Once the A1 field is updated with the next entry from the Data list, the Analysis macro will populate values on the 'Forecast' worksheet for each engine. I then want to record the 'Run Average' line for each engine on the 'Results' worksheet.

My trouble is that I'm not sure how to write the code to be dynamic enough to run through the entire data list (regardless of how many engines are displayed in the list).

Any help or advice would be extremely appreciated.

[JosephP]

haven't tested but something like this I reckon

Please Login or Register  to view this content.

[andyma80]

Wow, thank you for the quick reply. I have made the necessary adjustments and it looks like it will work perfectly!! Although what would I need to change in order to paste the value for the results instead of the formula? Because when I run it, returns #REF.

[JosephP]

change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[andyma80]

Perfect!!! I can't thank you enough!! I've been stumped on that for a week now. My next problem is to speed up the process (With 500 entries it can take awhile) but that's a whole other topic that I will tackle another time.

Thank you for your time!! --Andy