Kimberlieh
New Member
- Joined
- Mar 5, 2011
- Messages
- 12
Background information:<o
></o>
About every 6 months the Fed and State wage and hour divisions publish a new wage rate booklet specifying the required pay rates per work classification. I am using Microsoft Office Professional 2010 and with each new publication since 2009, I have been creating a new Excel worksheet, within my existing payroll workbook, by copying the previously created worksheet and naming the new one with the month and year, to reflect the publication date. Then I make any necessary changes so my sheet matches the publication. Then each subsequent bid letting I add the bid date in column “B”, which is labeled “Bid Date” then in the same row I enter the “Appendix” pay rates in columns C:L for both Fed and State per work classification. Currently I only have (2) work classifications on my publication sheets so if additional work classifications are added, additional columns beyond “L” will be inserted, however, since there are so many more classes, I will have to find another way to work with the data. <o></o>
Macro, for Microsoft Office Professional 2010, needed to accomplish task:<o></o>
I have struggled with numerous approaches trying to develop ways to have the process automated and though I am grateful for what I have learned so far, I am finally giving up, swallowing my pride, and asking for help to get a macro to do the job for me… Since calculating pay rates gets a bit complicated, the following is the flow of events needed, from a macro, to get pay rate information for my worksheet. <o></o>
· User enters <o></o>
a. The Bid Date in the active worksheet, “Project Info”, in column “L”, which is labeled “Bid Date” and the Project Class, i.e. “BOLI” or “Fed Aid” in column “M”, which is labeled “Project Class”. <o></o>
· Macro <o></o>
a. Look across column “B” “Bid Date”, in all the previously mentioned publication worksheets and find the match for the “Bid Date” the user entered in the “Project Info” worksheet.<o></o>
· Macro <o></o>
a. Determine the “Pay Calculations” for each work group, in this case column “R”, (Row count 5 from the “Pay Calculations” column of each work group of the “Project Info” worksheet), the “Base Pay Rate” and the “Fringe Rate”.<o></o>
i. If the Project Class is “Fed Aid”, look at the “county”, in column “K” of the “Project Info” worksheet, labeled “Project County”, and find the same county on the previously identified publication worksheet in column “O”. With the match, in say for example, “O28”, if “P28” = “Appendix” then after again matching the “Bid Date” in the publication worksheet in column “B”, in the same row, compare the “BOLI Pay Rate” in column “I” + “BOLI Fringe Rate” in column “J” against the “DB Pay Rate” in column “E” + “DB Fringe Rate” in column “F”. - Whichever result is greater; “BOLI” or “DB” will populate the “Project Info” worksheet with “BOLI” or “DB”. In the cell, in column “N”, Pay Calculations”, the “Base Labor Rate” amount and in column “S”, and the “Fringe pay rate” in column “U”. <o></o>
ii. If the Project Class is “Fed Aid”, look at the “County”, in column “K” of the “Project Info” worksheet, labeled “Project County”, and find the same county on the previously identified publication worksheet in column “O”. With the match, in say for example, “O28”, if P28 is a pay rate amount, then locate the “Bid Date” in the publication worksheet in column “B” again, and in the same row, compare the “BOLI Pay Rate” in column “P” + “BOLI Fringe Rate” in column “Q” against the “DB Pay Rate” in column “E” + “DB Fringe Rate” in column “F”. - Whichever result is greater, “BOLI” or “DB” will populate the “Project Info” worksheet. “BOLI” or “DB” in the cell, in column “N”, Pay Calculations”, the “Base Labor Rate” amount in column “S”, and the “Fringe pay rate” in column “U”.<o></o>
iii. If the Project Class is “BOLI”, look at the “county”, in column “K” of the “Project Info” worksheet, labeled “Project County”, and find the same county on the previously identified publication worksheet in column “O”. With the match, in say for example, “O28”, if P28 is “Appendix” then after again locating the “Bid Date” in the publication worksheet in column “B”. Populate the “Project Info” worksheet with “BOLI” in the cell, in column “N”, “Pay Calculations”, and the “Base Labor Rate” amount in column “S”, with the “Fringe pay rate” in column “U”. <o></o>
iv. If the Project Class is “BOLI”, look at the “county”, in column “K” of the “Project Info” worksheet, labeled “Project County”, and find the same county on the previously identified publication worksheet in column “O”. With the match, in say for example, “O28”, if P28 is a pay rate amount. Populate the “Project Info” worksheet with “BOLI” in the cell, in column “N”, “Pay Calculations”, the cell in column “S”, “Base Labor Rate” from the publication P28 in column “S”, and the cell in column “U”, “Fringe pay rate”, from the publication Q28.<o></o>
Once the pay rate is determined for the Labor, the next pay group’s rate needs to be found and so on until all the pay group rates for the project are populated on the “Project Info” worksheet. <o></o>
The following columns are used in all rate calculations<o></o>
· Column “L”, “Bid Date”<o></o>
· Column “M”, “Pay Calculation”<o></o>
· Column “K”, “Project County”<o></o>
The following columns are columns are how many are used per pay group<o></o>
· Column “N:U”<o></o>
· Column “V:AC” <o></o>
· The 5th column defines the pay group.<o></o>
Any help on this macro is greatly appreciated…<o></o>
></o>About every 6 months the Fed and State wage and hour divisions publish a new wage rate booklet specifying the required pay rates per work classification. I am using Microsoft Office Professional 2010 and with each new publication since 2009, I have been creating a new Excel worksheet, within my existing payroll workbook, by copying the previously created worksheet and naming the new one with the month and year, to reflect the publication date. Then I make any necessary changes so my sheet matches the publication. Then each subsequent bid letting I add the bid date in column “B”, which is labeled “Bid Date” then in the same row I enter the “Appendix” pay rates in columns C:L for both Fed and State per work classification. Currently I only have (2) work classifications on my publication sheets so if additional work classifications are added, additional columns beyond “L” will be inserted, however, since there are so many more classes, I will have to find another way to work with the data. <o
></o>Macro, for Microsoft Office Professional 2010, needed to accomplish task:<o
></o>I have struggled with numerous approaches trying to develop ways to have the process automated and though I am grateful for what I have learned so far, I am finally giving up, swallowing my pride, and asking for help to get a macro to do the job for me… Since calculating pay rates gets a bit complicated, the following is the flow of events needed, from a macro, to get pay rate information for my worksheet. <o
></o>· User enters <o
></o>a. The Bid Date in the active worksheet, “Project Info”, in column “L”, which is labeled “Bid Date” and the Project Class, i.e. “BOLI” or “Fed Aid” in column “M”, which is labeled “Project Class”. <o
></o>· Macro <o
></o>a. Look across column “B” “Bid Date”, in all the previously mentioned publication worksheets and find the match for the “Bid Date” the user entered in the “Project Info” worksheet.<o
></o>· Macro <o
></o>a. Determine the “Pay Calculations” for each work group, in this case column “R”, (Row count 5 from the “Pay Calculations” column of each work group of the “Project Info” worksheet), the “Base Pay Rate” and the “Fringe Rate”.<o
></o>i. If the Project Class is “Fed Aid”, look at the “county”, in column “K” of the “Project Info” worksheet, labeled “Project County”, and find the same county on the previously identified publication worksheet in column “O”. With the match, in say for example, “O28”, if “P28” = “Appendix” then after again matching the “Bid Date” in the publication worksheet in column “B”, in the same row, compare the “BOLI Pay Rate” in column “I” + “BOLI Fringe Rate” in column “J” against the “DB Pay Rate” in column “E” + “DB Fringe Rate” in column “F”. - Whichever result is greater; “BOLI” or “DB” will populate the “Project Info” worksheet with “BOLI” or “DB”. In the cell, in column “N”, Pay Calculations”, the “Base Labor Rate” amount and in column “S”, and the “Fringe pay rate” in column “U”. <o
></o>ii. If the Project Class is “Fed Aid”, look at the “County”, in column “K” of the “Project Info” worksheet, labeled “Project County”, and find the same county on the previously identified publication worksheet in column “O”. With the match, in say for example, “O28”, if P28 is a pay rate amount, then locate the “Bid Date” in the publication worksheet in column “B” again, and in the same row, compare the “BOLI Pay Rate” in column “P” + “BOLI Fringe Rate” in column “Q” against the “DB Pay Rate” in column “E” + “DB Fringe Rate” in column “F”. - Whichever result is greater, “BOLI” or “DB” will populate the “Project Info” worksheet. “BOLI” or “DB” in the cell, in column “N”, Pay Calculations”, the “Base Labor Rate” amount in column “S”, and the “Fringe pay rate” in column “U”.<o
></o>iii. If the Project Class is “BOLI”, look at the “county”, in column “K” of the “Project Info” worksheet, labeled “Project County”, and find the same county on the previously identified publication worksheet in column “O”. With the match, in say for example, “O28”, if P28 is “Appendix” then after again locating the “Bid Date” in the publication worksheet in column “B”. Populate the “Project Info” worksheet with “BOLI” in the cell, in column “N”, “Pay Calculations”, and the “Base Labor Rate” amount in column “S”, with the “Fringe pay rate” in column “U”. <o
></o>iv. If the Project Class is “BOLI”, look at the “county”, in column “K” of the “Project Info” worksheet, labeled “Project County”, and find the same county on the previously identified publication worksheet in column “O”. With the match, in say for example, “O28”, if P28 is a pay rate amount. Populate the “Project Info” worksheet with “BOLI” in the cell, in column “N”, “Pay Calculations”, the cell in column “S”, “Base Labor Rate” from the publication P28 in column “S”, and the cell in column “U”, “Fringe pay rate”, from the publication Q28.<o
></o>Once the pay rate is determined for the Labor, the next pay group’s rate needs to be found and so on until all the pay group rates for the project are populated on the “Project Info” worksheet. <o
></o>The following columns are used in all rate calculations<o
></o>· Column “L”, “Bid Date”<o
></o>· Column “M”, “Pay Calculation”<o
></o>· Column “K”, “Project County”<o
></o>The following columns are columns are how many are used per pay group<o
></o>· Column “N:U”<o
></o>· Column “V:AC” <o
></o>· The 5th column defines the pay group.<o
></o>Any help on this macro is greatly appreciated…<o
></o>
