First Function - find cubic area from rows and sum the range

[DeaJL]

Hi,

Hope this is right forum for this.
I'm trying to write my first function.

Here's my brief

I Have a couple of thousand workbooks (production orders actually, in case you're wondering why so many) which are layed out identically.

In each book there is a sheet called "PO P1"
There is a range (used row by row and layed out as per the attached file).
The range is "P15:S48" There is also an order number in cell E5 of the same sheet.

It represents sizes and quantities of components (row by row) with the size data entered in millimetres.

I'm trying to write my first function which needs to:

• Find the cubic area in metres of each row then multipy that result by the quantity for that row to give total cubic metres for that row.

• Do this for each row with data in it (not all rows in the range are used)
• Then sum all row totals with a grand total for total cubic metreage for that range.

I'm not sure it's possible, (though I think I've read this). but I would then like to apply that process to each workbook in a given folder with each line identified by it's order number
(cell E5 of the same sheet).

Hopefully, this will then allow me to end up with a results sheet (current new book probably when code is run) with 2 columns showing Order number and next to that the total cubic meterage result.

I have tried to declare the elements but to be honest I'm out of my depth understanding what should be declared as what.

Never done anything like this from scratch so getting my "As Ranges" and As Sheet mixed up. let alone actually getting a result at the end of it!

Only last detail which may be relevant is that sometimes users have entered shapes or other line drawings in the blank areas of this section which may mean some of rows may be corrupted in regards to being relevant to the task at hand. Is there anyway that some error trapping could occur which could evaluate if each row appears complete before it is included / passed from the funcion?

I appreciate that this is a big ask but having to try and extract this information from all these books retrospectively is a monumental task and costings and decision making timeframes are not helping me in the slightest.


Any help will be humbly and graciously accepted.

Dean

[mrice]

Maybe this will help.

The code loops through all the files in a folder opening each workbook in turn.

It activates the first sheet and does the caculation looking for the presence of four numbers in any one row as being the criteria for the multiplication to be done.

[DeaJL]

Sir, I am absolutely indebted to you.
I am really grateful for your input.
This may have just saved me hundreds of hours of work.

I can't believe taht there is so little code involved. VBA leaves me speachless it truly does.

can you just confirm a thought for me.
E1 I'm assuming stays as is
D1 is where is will enter my path.

I'm having trouble getting it to run but wonder if this is due to my input.

Kind regards and huge admiration

Dean

NOTE: the full path I'm using is:
Z:\ORDERS\4500 - 4999

I have entered this in D1 but the code doesn't seem to run unless I'm just being impatient. Would data start appearing straight away?

[mrice]

Data should start appearing straight away.

Looking at your reply, it may be that you are missing a trailing \ on your path description.

It should be

Z:\ORDERS\4500 - 4999\

and not

Z:\ORDERS\4500 - 4999

Does this help?

[mrice]

Just read your response again.

You need to overwrite the value in E1 with your path.

The text in D1 is just a label to help explain what's going on.

[DeaJL]

Martin Martin Martin!

I'm gushing here I know but I've got to tell you, those few lines of code you kindly provided just saw me through all our orders going back to 2003
(One Thousand Four Hundred and Eighty One workbooks in all !)

Started at 1.47 AM (Yes AM don't ask)
Finished at 2.12 AM

Equivailant manual task was:

Open excel
Navigate to orders folder
Double click file wait for it to load
Scroll to and Select the sheet (some way down the sheet tabs)
Select a cell in some spare space next to my range
Enter the syntax for my humble cubic metre function
click and comma through the cells in the range, close the brackets
Autofill (Drag) the leading cell down the list
(going through the whole process again if I saw any corrupt lines (as I showed)
Sum the whole column
Copy the result cell
Alt Tab into my result book
Right click paste special the values
Go back to the order book
Close the order book
Click no to save

(wasn't even bothering with getting the order number in on the act!)

Then.................

Do it aaaaallllll oveeeeerrr again......and again..........and again.........

Suffice to say that in just over 3 days (admitidly not continuously but...)
I had managed to get through 40 orders or so. (allowing for other daily chores)

Making the total...............Jaw Dropping 25 minutes for all 1481 books (including juggling with changing the path a number of times to get started on the next folder involved AND getting the order number sitting along side as well), completely and utterly and mind bendingly ridiculous!


The data you have helped to extract has just helped our business establish that since 2003, we've gone though over 42 cubic metres of material and that each order averages out at 0.022 cubic metres per order.

This is going to allow us to immediately provide some prosepective new suppliers who are looking for quantities before they will commit to price structures.

As a small manufacturing business in the UK, every saving we can make is a big one.

I can't thank you enough.


If there was a section on the forum for the "The Golden Code Awards" Then this would be right at the top of my list.

If you ever think, want or somehow just feel the need to know the real worth of your actions then the above represents the real worth to me for the time you took in assisting a newbie.

Thank you

Dean

PS. Got to tell you it was completely bonkers watching each of these workbooks open on their own before my very eyes.

As fast as I could click no to save changes the next one was opening!

Mad ! Totally Mad!