Forecasting with VBA or Power Pivot in Excel

[Emilio30]

Dear members,

I am currently working on forecasting in excel, maybe due to the large amount of data (11K cells) and fomulas (a lot conditional formatting) my excel document is running slow.

I was wondering if it was possible to calculate in VBA (given the historial data of the products) the future forecasts.
For the forecasts calculation Im currently using 8 weeks moving average with a lot of conditional formatting, like when Im in current week X take the sales from this week to this week, if its a new product then to take the values from a separate sheet only meant for new products (which is more manual since are new launches), also when its a new product and there are no current sales for the past 8 weeks; to take the values from the last 8 forecasted weeks but once there are 8 weeks of actual sales to take them instead.

I wanted to check if something like this is possible to automate (Either with macros, VBA or power pivot) since there are currently so many conditional formatting involved and would like to make the document run faster and with less formulas accross all the sheet.

Any suggestion or support is highly appreciated.

Thank you!

 

[BobBridges]

Eleven thousand cells doesn't sound like a lot to me, if you're just talking about Excel formulae, VLOOKUPs and like that. Conditional formatting I know less about; I use it a little, but not much.

But maybe I misunderstood. Do you already have a VBA program, and that program is filling in those cells' values one cell at a time? If so, yeah, that would really slow it down—and there's a way to make it so much faster you won't believe it at first. It's a little more work on the coding side, but well worth the effort if your program is trying to populate more than a few dozen cells. Create an array of the size you want, then pour that into the worksheet. Something like this:

' Initialize.
Dim ar()
Set owb = ThisWorkbook
Set ows = owb.ActiveSheet
Set ocs = ows.Cells

' Populate the array.
Redim ar(1 to 9999, 1 to 35) 'I'm just making these numbers up, of course
For jr = 1 to 9999
  For jc = 1 to 35
    ar(jr, jc) = "Row " & jr & ", col " & jc 'or whatever
    Next jc
  Next jr

' Now put the array into the worksheet.
Range(ocs(1, 1), ocs(9999, 35)).Value = ar

This, unlike putting the values into the worksheet cells one by one, is blindingly fast.

But it helps you only if I'm addressing the right problem. If not, maybe you need to post a sample so people can see what you're talking about.