(Solved) – Resuming data from a database is extra slow – VBA

(solved)-–-resuming-data-from-a-database-is-extra-slow-–-vba

So, i’ve got a Workbook that will have more than 1500 rows of data in it’s database… That’s… not much, it is very fast to run that;

I’ll have to make a resumed table of the data, but the user will have to choose some of the filters for the table, there are three major things that will have to happen:

1st the user will select an “OT” (That’s the name the company gives to their clients, OTXXXXXX), that will have to trigger some vba code which will preset a table

2nd There will be a 3 row header, with some very simple formula in the last header’s last 2 rows and the first will be the products of that client

3rd Now we are f****, the rows part will have a “header”(Is that the name?) with a 1 column width, but that little, not so little, sh**, has around 140 rows plus with invoices, and for each Invoice x Product cell there will be a number of boxes that went off

Now, i´ve already tried doing it “manually” on VBA, reading each cell’s row and column, getting the references, going to DB and bringing back the number of boxes, an i7 9750H with 16Gb of ram disliked that, so I’m not able to give that to the company (their Desktops runs way slower than my laptop). So i´ve tried to use a formula to do that, I’ve tryed an array formula and a matrix formula, both worked meh, around 2-3 min of processing, which would translate to 8-10min it´s fine… Buuuut the database keeps increasing and each change on it triggers the calculation, and disabling it would be even worse…, I can’t afford that time loss, so i’m litterally lost.

I don’t know what to do, just help

Please ;-;

Leave a Reply

Your email address will not be published. Required fields are marked *