Please refer to the attached excel and word documents for a complete description and feel free to ask me any questions...
We want to modify the existing macro (GenerateMetricsAndQuotes) to create a new sheet called RATES, based on the current BUDGET
The current macro (GenerateMetricsAndQuotes) creates QUOTE and METRICS based on BUDGET
The new macro still creates QUOTE and METRICS and the additional RATES based on BUDGET
The sequence to create RATES is:
1. Create and delete columns to match the given tab RATES
2. Find the same TASK in MATERIALS and LABOUR. For example the task: “Flashings Colorbond”
3. Find pair of records (the pair if formed by a record under MATERIALS and a record under LABOUR) with the same text. For example “Roof Safety Mesh (90m2) Per Roll “. Sometimes there are records with only one component. For example: “CL 4 Type 17 12x65 (each)” is only in MATERIALS. “Labour Penetration_Large. 1menx0.5day($275)” can be found only under LABOUR. “Crane” is only under PRELIMINARIES
4. Move the LABOUR cost component of the pair to the new column “Labour Rate” sitting next to its corresponding MATERIALS component. If there is no pair but only MATERIAL component or LABOUR component, it is find, just place it and leave the other cell empty (refer to spreasheet “REPORT” for illustration of this case)
5. Leave the MATERIAL cost of the pair on the existing column “Material Rate”
6. The old record (row) for labour gets deleted
7. The record under PRELIMINARIES remain unchanged
8. The rows from “Not Coded” to the bottom row get deleted
9. The new worksheet RATES will have formulas that will calculate the MATERIAL COST = MATERIAL RATE x QUANTITY, and LABOUR COST = LABOUR RATE x QUANTITY.
10. The new worksheet RATES will add MATERIAL COST + LABOUR COST under the new column TOTAL
11. The yellow cell next to margin ie: is a formula. Its value is taken from a cell on sheet “QUOTE”. That cell, its location and value is determined by the existing macro. Its position changes on the different projects so the new program needs to name that cell with the name GPM_PROJECT and copy its value to the sheet RATES
12. Formatting as per the RATES sheet provided on the REPORT workbook
Please ensure that the current work of the existing macro GenerateMetricsAndQuotes does not get affected in anyway by the addition of new routine (it works very well at present).
Please note that the current macro has been tested to execute budgets with upto 200 scopes (very large budgets) and works well and a reasonable speed. Please ensure the new routine is also capable to execute for large budgets without detriment to its speed or accuracy.
Dear client, I have sound knowledge in Excel and I have previously carried out a number of projects in excel using macros and formulas. I ensure you will be happy after hiring me. Kindly contact me. Roy
11 freelancere byder i gennemsnit $35 på dette job
I am an Excel and VBA specialist with a lot of experience in creating complex formulas and macros. You may check some of my projects here: [login to view URL]
Hi sir/madam, Im expert in microsoft office, expecially in excel and word. I can do the project wtih quickly and very well. I hope you interest about my bid. Thanks