Hi All,
I previously had a problem that was solved with the wonderful help of Greg M, a valued forum contributor. He has been on vacation and may be too busy to help me, and I don't want to keep bugging him. Here is a link to that thread to help with background info:
http://www.excelforum.com/showthread...=645094&page=2
I am trying to retrieve data from many "Job cost" workbooks to a master "Job Cost Summary" workbook. The problem is that the data I am trying to retrieve is almost always in defferent cells. However, there are some consistencies that I outlined in the link above, but will repeat here:
1. The data will always be found in the range B2:K25.
2. The Descriptions are always in column B and they are always in the same order from top to bottom starting in row 8.
***But they change rows based on what type of cost is entered on that job. For example, "Fabrication Labor" then "Erection Labor" then "Materials" will be in rows 8, 9, and 10 respectively. But if there is no "Erection Labor" on the next job, "Materials will move up to row 9.
(my thought on this is a code/formula that searches column B for a Description, if can't find it then Data=0, if it finds it begin searching that row for the data)
3. Regarding the columns where the data is stored, again the order is always the same from left to right staring with Description in column B, then comes Material Wt, Man Hours and Actual Cost.
***But, Material Wt, Labor Hours and Actual Cost are never in consistent columns, and they will not necessarily be nest to each other. The only consistency is that Actual Cost will be to the right of Material Wt and Labour Hours. AND, there are only two data entries per description: Material Wt and Actual Cost OR Man Hours and Actual Cost.
(continuing my thought above, search the row from left to right from C to K for the first non-zero value and that will be the Material Wt or the Man Hours, then search from right to left from K to C and the first non-zero value will be the Actual Cost)
I feel like if I can think it through, it can be written???
I am attaching some actual Job Cost files and the Job Cost Summary, which already contains the VBA code to retrieve bid data (module 1) which works beautifully (thanks again Greg M). I attempted to start the code to retrieve the cost data (module 2) but ran into the problems above.
Thanks,
Natasha
Bookmarks