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

Wow!, i really don't know what you are trying to do there but the select
case concept is this:
Sub SelectCaseConcept()
Select Case I & Current_Job_Type
Case is Heavy
WorksheetFunction.VLookup(d, e, Straight_Time_I, False) =
H1
Case is Commercial
WorksheetFunction.VLookup(d, e, Straight_Time_I, False) =
C1
Case is SomethingElse
DoSomethingElse
Case is....etc
?.....Just keep adding conditions
End Select
End Sub

Does this help?
'The Code Cage' (http://www.thecodecage.com)


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=6858

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.

"The Code Cage Team" wrote:


Wow!, i really don't know what you are trying to do there but the select
case concept is this:
Sub SelectCaseConcept()
Select Case I & Current_Job_Type
Case is Heavy
WorksheetFunction.VLookup(d, e, Straight_Time_I, False) =
H1
Case is Commercial
WorksheetFunction.VLookup(d, e, Straight_Time_I, False) =
C1
Case is SomethingElse
DoSomethingElse
Case is....etc
?.....Just keep adding conditions
End Select
End Sub

Does this help?
'The Code Cage' (http://www.thecodecage.com)


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=6858

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.


--
butrfly_kis3
------------------------------------------------------------------------
butrfly_kis3's Profile: http://www.thecodecage.com/forumz/member.php?userid=8
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=6858

butrfly_kis3;22920 Wrote:
.....
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, nice to have you on board, now that you are here you can upload a
workbook so we can see your structure, please make sure there is no
personal or sensitive data in it, all we/anyone here needs is the
structure/layout and some dummy data resembling what you are using, you
should leave any range names or sheet names the same as this will help
us help you.

To upload a workbook, click reply then add your few words, scroll down
past the submit button and you will see the Manage Attatchments button,
this is where you get to add files for upload, if you have any trouble
please use the 'Contact Us' (http://www.thecodecage.com/hesk) form.


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=6858

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.

"Simon Lloyd" wrote:


butrfly_kis3;22920 Wrote:
.....
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, nice to have you on board, now that you are here you can upload a
workbook so we can see your structure, please make sure there is no
personal or sensitive data in it, all we/anyone here needs is the
structure/layout and some dummy data resembling what you are using, you
should leave any range names or sheet names the same as this will help
us help you.

To upload a workbook, click reply then add your few words, scroll down
past the submit button and you will see the Manage Attatchments button,
this is where you get to add files for upload, if you have any trouble
please use the 'Contact Us' (http://www.thecodecage.com/hesk) form.


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=6858

Please upload a dummy workbook with your structure and formatting on
your log in at the forum :)


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=6858