Select Case Statement

[Katie]

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

 

[Katie]

I am trying to create an excel spreadsheet that will be in a sense "error
proof" doing the work behind the scenes.
I would like the sheet to look at the year, the job type, quanity and
description.

If the job type is heavy then it will take the year and look up the
description on a different spreadsheet and bring back the rate (labeled
straight_time & the year). I would then like it to calculate the total price
by taking the rate times the quanity times the hours.

If the job is commercial it needs do the same as above but for any hours
over 8 it needs to calculate it based on the overtime rate for that year. I
have the spreadsheet set up to allow the user to pick from 2008-2014.
I have limited experience with select case but I was not sure how to go
about writing this out.
It would be nice to have it run in the background and only have a formula in
the necessary columns.
Hopefully this explains what I am trying to do a little clearer.

 

[Katie]

I have created functions in the past that allowed me to do that. Such as
calculating the days between two dates. I just want it to perform two tasks
based on certain variables.