Quantity Surveying Issue

**Therock1** · 09-01-2010, 10:07 AM

I am trying to work out a process to help with a quantity surveying for the steel fabrication component of a project I am working on.(4000T)

I need to extract the data from the attached sheet for main members of steel only i.e.-UB, UC

Anything in the "Item" column that contains the letter "r" needs to be excluded but i still need all the data to line up with each particular Mark Number.

The method of measurement includes for the weight of the main raw member only .

I have about 1000 spreadsheets to review so any help to develop a macro would be greatly appreciated.

Thanks
Ben

**tenk283** · 09-02-2010, 12:15 AM

Would you want all the data from the 1000 spreadsheet collated at once or just individual sheets?

Just trying to think what loops you would run. If you had all the spreadsheets in one place, it would be possible to loop through all of them, pulling the applicable data and then printing to a separate file. Because running one macro a thousand time would be a pain.

**Therock1** · 09-02-2010, 06:56 AM

Originally Posted by tenk283

Would you want all the data from the 1000 spreadsheet collated at once or just individual sheets?

Just trying to think what loops you would run. If you had all the spreadsheets in one place, it would be possible to loop through all of them, pulling the applicable data and then printing to a separate file. Because running one macro a thousand time would be a pain.

Yep definitely, I also found some more info today that places a cost code against the raw material members which is another yet another series of spreadsheets.

**tenk283** · 09-02-2010, 07:35 AM

Is the formatting consistent from sheet to sheet?

ie. Are those "r" members always located in a merged C/D cell?

I'll post some code tomorrow night. I didn't bring my personal.xls home (its on my work computer), i forgot. So i'll stick with pseudo-code to start with.

Please Login or Register  to view this content.

Next thing is, i'm not too strong on formatting. I'm not sure how to handle all those merged cells. xlPasteAll might cope and put everything in the right place. Might need some assistance on that.

I'm also assuming you want those serial numbers in bold above the members. If not, then i'll have to rethink.

Lastly, are all these spreadsheets the same size? (400 rows-ish)

**Therock1** · 09-02-2010, 07:45 AM

yep, all formatting is the same, the merge cells drive me batty and the r cell is consistent.

Correct, about the bold number that is the Mark number of the piece of steel and the SWP items in the field below are used for the assembly.

SWP-51-1014 equates to Structural Workpack 08- Phase 51-Mark Number 1014.
The designers got a hold of the naming convention for the project before i started so t was too late to change it unfortunately.

The size can vary but the 400 rows is one of the bigger spreadsheets.

Will get you the copy of the 3 x spreadhseets required and the theory behind it when i am back at work tommorrow.

Appreciate your help.

**tenk283** · 09-02-2010, 07:55 AM

No promises :D Its only because a) I work as an estimator in the timber industry and quantity surveying is my day job (just for residential) and b) i've done some very similar macros just in the past couple of weeks.

Have you tried any code thus far? Do you have anything written?

**Therock1** · 09-02-2010, 08:08 AM

No, i can put spreadsheets together with formulas etc but once it goes to code across mulitple sheets it loses me because i dont do it often enough. I have done a quick course on it but not done anything in depth.

I want to get it exact as i know the contractor has claimed 400T++ (~ $2M) over what is actually there

I usually try to get the control engineers at work to do it but everyone has left the projeect a tthe end so have to try and work it out...wont hurt me to start learning it i suppose.

Talk to you tomorrow.

**tenk283** · 09-03-2010, 04:07 AM

Please Login or Register  to view this content.

Need some serious help with the errors. I know what I want to do, I just can't put it into the right VBA terms.

I just added in some user interface to help track the progress of the macro. This way you will know if the macro is working, nothing worse than letting a program run for a couple of hours with no visual sign it is working (except the hard-drive humming) only to come back and find out it has been stuck in the same place since 5mins after you started it.

**Therock1** · 09-03-2010, 10:41 PM

Thanks fr that i will give it go ver the weekend.

As for the errors i will try and get the senior control engineer to have look at it.
The can usually iron out the errors for me.