I am trying to create a bid sheet that will do several calculations behind
the scenes. I believe that select case will be the most appropriate however I
am struggling with the entire concept. What I have so far is this:
The three variables are Year, Job Type & Quantity
If the current_job_type is Heavy, this is the easiest part, you just look up
the description and pull back the straight_time by what ever year it is. The
way I have the spreadsheet set up right now is a column for each year ie
Straight_Time_2008, Overtime_2008, Double_Time_2008; Straight_Time_2009 etc.
If the current_job_type is Commercial, and quanity is less than or equal to
8, then you look up the description and pull back the straight_time by
whatever year it is.
If the current_job_type is Commercial, and the quantity is greater than 8
you need to look up both the straight_time_year and the overtime_year and
store for later use.
Once these items are finished I want the program to calculate the total
payrate.
If the job_type is heavy it should be (Quantity*hours)*H1(rate)
If the job_type is Commercial & the quanity is less than or equal to 8 it
should be (Quantity *hours)*C1(rate)
If the job_type is Commercial & the Quanity is greater than 8 it should be:
(Quantity*hours)*C1(rate) + ((Quantity-8)*C3

Dim Year As Date
Dim Description As String
Dim Quantity As Integer
Dim Job_Type As String
Dim Equip_and_Labor As String
Dim R As Integer
Case 1 Year = I & Current_Job_Type = Heavy
Case 2 Year = I & Current_Job_Type = Commercial (& Quantity <=8)
Case 3 Year = I & Current_Job_Type = Commerical (& Quanity 8)

Dim H1 as long
Dim C1 as long
Dim C2 as long
Dim C3 as long

I = Year
Q = Quantity
d = Description
J = current_Job_Type
e = Equip_and_Labor


Select Case Heavy
Case (1)
WorksheetFunction.VLookup(d, e, Straight_Time_I, False) = H1

Select Case Commercial
Case (2)
WorksheetFunction.VLookup(d, e, Straight_Time_I, False) =C1
Case (3)
WorksheetFunction.VLookup(d, e, Straight_Time_I, False) =C2
WorksheetFunction.VLookup(d, e, Overtime_I, False)= C3

Do Payrate
Total_Price = (Quantity * Hours) *H1
(Quantity * Hours)*C1
(Quantity *Hours)* C2
(Quantity-8)* Hours)*C3