VBA Loop for each to build forecast

[natekris8183]

I have a data set that includes Column1 (Dates, week-to-week starting on Mondays), Column2 (product by SKU), Columns3 (product status, active... this will eventually be left out as soon as I finish the VBA to permit a list to be built allowing only active products to be selected in the data validation drop down), Column4 (Source: legacy or dealer). The SKUs are unique to the products obviously. I need to create a list range of data set to input into the Worksheet Function Trend based upon dates prior to the current date in the table range (RawSalesDataTbl[@Week]). This will provide a running trend to juxtapose against our proposed forecast. The complication is the data set is not linear thus I cannot simple name a range of "y" values and "x" values. The easy one is naming the proposed "X" value as it will be the next week value, again determined at RawSalesDataTbl[@Week].

 

[natekris8183]

It does make sense, except for we wouldn't want to trend the data past the most current date with Actual data available. So I needed for it to specify the range based upon the row with the last row of data for the SKU, which I was able to write fine. I've actually now fixed the code. Unfortunately the runtime is rather long though, do to all the qualifiers. I was able to name a range in the sheet to use in the Worksheetfunction.Trend for the X's, Y's, and new X values. My biggest complication came when the SKU names contained double quotes. I was trying to use an evaluate function in the macro and the double quote inside the string thread caused an mismatch type error in the evaluation of the worksheet function. I posted about it http://www.mrexcel.com/forum/excel-questions/921049-double-quote-syntax-error-evaluate-visual-basic-applications.html there if you'd like to see that discussion forum. Nonetheless, I have a solution, I just need to figure out how to pare down some of the IF statement qualifiers to reduce the runtime. There's 18,000+ lines of data, so that really slows things down.

 

[michaelsmith559]

Glad to here you got it fixed.

 

[michaelsmith559]

In your previous code, what I was trying to get at, is that it looks like your actuallist and datelist values were not being put anywhere, just being looped through. I did not see where they were being put into a range or anything. I know you have it fixed now, but you need to speed it up. Would it be possible to fill two arrays, one with actuallist values and the other with datelist values and then perform trend on the arrays?