Remove 11000 external array formulae & reduce process ti

[PeregrinTook]

Ok I’ve made quite a few posts now on this forum and am delighted to say that virtually all my queries have been solved by a number of resident VBA pro’s. Most of the posts so far have been in relation to a particular project I’ve been working on where I’ve been automating a number of reports from exported Access queries. Now however, since I’m the only person on my project who has the faintest idea about VBA, I’ve been asked to start looking at automating some more reports for other people – and since the bulk of what I’ve learned so far has been on an extremely ad-hoc manner I thought it best to try and start this first task in a much more structured fashion. Which is what leads me straight back here, to the home of the experts!

Current process for generating aged stock summary:

Export csv file (currently around 5500 rows) from oracle db then open in excel & copy all to tab ‘stock’ on totals.xls
Sort by stock_entry_date descending
Insert a column next to stock_entry_date and manually enter a category value next to each date from 1 (less than one month) to 8 (over 2 years) or 0 for no date
Resort by location_code ascending
Cut each set of rows with the same location_code and paste to appropriate tab of the same location_code name – end result is 39 tabs populated each populated with its own data, and tab ‘stock’ is blank.
Save spreadsheet

Open aged stock summary.xls & hit update external links
Go and make a cup of tea, perhaps even a sandwich – heck possibly time for a game of golf when this puppy opens!
This summary sheet also contains 39 tabs with location_code names, and each has a table in it showing the sum of all stock across each product (31 columns) against the 9 date ranges using array formulae back to the corresponding location tab on totals.xls. There are in fact just shy of 11,000 external array formulae to be updated each time this sheet is opened – then of course there are the other 9 higher level summary tabs which link to these 39 location_code tabs, but they are nothing in comparison & there are no array formulae there.

It currently takes someone between 30 mins & an hour to sort out the totals.xls update. Then whenever any of the managers want to see the high level summaries they don’t know how recent the data is so they always hit update, which takes between 15-30 mins and can hang their whole systems occasionally (granted the pc’s they use could do with being brought into the 21st century...).

So how to speed things up a little? Well I have an idea but I thought before I got started on coding I would seek some expert opinions on the way forward. Here’s my idea:

Have a blank version of aged stock summary.xls which opens at a ‘file summary’ tab
Firstly all array formulae must be stripped out as external links are no good (especially 11,000 array ones!) and remove need for totals.xls completely
Tab ‘file summary’ has three user-maintained tables in it, for location codes (currently 39) as rngLocationCodes, stock codes (currently 31) as rngProductCodes and date codes (9) as rngDateCodes, which auto-updates based on now() function
User who normally carries out update process exports csv file to pre-defined directory clicks ‘update’ button which kicks off VBA routine to do the following: (mixture of VBA/plain english - just rough draft at the moment!)

Sub Update()
Dim all variables/strings etc...

Open csv file

‘compare each value in column N to the predefined strTimeCategory (from rngDateCodes) and insert into column M (dead field, always comes out blank from DB)
For i = Range("N" & Rows.Count).End(xlUp).Row To 2 Step -1
	If Cells(i, "N") <= strTimeCategory1 And Cells(i, "N") > strTimeCategory2 Then
		Cells(i, "M") = 2
	End If
	If Cells(i, "N") <= strTimeCategory2 And Cells(i, "N") > strTimeCategory3 Then
		Cells(i, "M") = 3
	End If
   Etc More Ifs
   ...
   For all 0-9 strTimeCategory’s
Next i

For each strLocCode in rngLocationCodes
	For each strProdCode in rngProductCodes
		For each strDateCode in rngDateCodes
			With csv file
				strAnswer = Evaluate("=SUMPRODUCT( (Q:Q=strLocCode), (C:C=strProdCode), (M:M=strDateCode))
			End with
         worksheets(strLocCode).range(vRow,vCol)=strAnswer
		Next strDateCode
	Next strProdCode
Next strLocCode
Worksheets(FileSummary).range(LastUpdateTime)=now()
Activeworkbook.SaveAs Filename:= strFilePathAndName & Format(Date, "yyyymmdd")
End Sub

Would still need to suss out how to define the paste range, but I’m thinking vRow & vCol will increment with each step of the defining for/next loop. Would also need to deal with error handling (ie worksheet doesn’t exist for newly created vLocCode etc).

But the above, after a great deal of refining, will hopefully populate each cell in the table of each tab in turn with a value rather than a formula or external link, thus making the opening time almost instant. I believe it should cut the update time at least by some (as opposed to array formulae within the spreadsheet which update on open) but I don’t know how much. This is where I’m looking for opinions!

Is the way I’ve suggested above a viable option, and do you think it will be significantly quicker than the previous method of array formulae? But the biggest question of all I guess, is – is there an easier way to do it?!?

All thoughts very welcome, as always - indeed the more opinions the better so lemme hear them please!!

All the best,
PT

 

[VicRauch]

Tony,
Two reasons I suggested If; IfElse. First, this is already coded with If statements, so just a little bit of tweeking. Second, I did not know that there was any difference between If; IfElse and Select/Case. With the If; IfElse, as soon as the True is found, all the rest of the IfElse statements HAVE TO BE SKIPPED. They are all Else statements. With the Select/Case, all the Case statements until the True is found must be processed, just like the If; IfElse. So my question is, why would the If; IfElse take longer than the Select/Case?

Thanks,