Select Case Statement
 

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

Select Case Statement
 

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

Select Case Statement
 

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

Select Case Statement
 

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

Select Case Statement
 

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

Select Case Statement
 

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

Select Case Statement
 

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

Select Case Statement
 

I really appreciate any help you can give me. I am attaching the
workbook. I had to place it in a zip file because it was too large.


+-------------------------------------------------------------------+
|Filename: Example Workbook.zip |
|Download: http://www.thecodecage.com/attachmen...attachmentid=1 |
+-------------------------------------------------------------------+

--
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

Select Case Statement
 

Sorry about the upload size thats been sorted now, i cant fathom out
what it is you are trying to do, can you give it to me idiot fashion,
what you want see happen, what data is supposed to be used and for what
and in which way and how you expect to see the result and where.


--
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

Select Case Statement
 

When the boss is bidding a job they need to assign a several crews to a
job.
1. Select year the work is to be completed.
2. Select job type
3. Select the description (ie labor & equipment)
These are all drop down boxes.
4. Identify the quantity of the labor & equipment.
5. Identify the number of manhours per day for the labor and
equipment.

Steps 1-5 should all be inputed by the end user.

This is what I need to have the workbook do. The end user is not very
computer literate and needs something that will be some what "error
proof" they are used to using Lotus notes and manually adding in
everything to include the calculations.

Calculations Needed:
1. Unit Price-based on the year and the type of job to find the rate.
Heavy jobs are always straight time. IE Year is 2008, Job type is Heavy
so look at the description and find it on the Equipment and labor sheet
and bring back the rate for Straight_Time_2008.
If the job is commercial anything over 8 hours is considered overtime
so if the manhours are 10 you need to find the straight time rate of
whatever year for the 8 and the overtime rate for the year for 2 hours.
2. Total price-this is based on the quantity, hours and the rate. IE
(Hours * Quantity)*Rate.


--
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

Select Case Statement
 

Ok, i'll take a look and see what i can come up with! ;)


--
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

Select Case Statement
 

Hello,

I am just trying to see if you have been able to come up with anything
thus far? I do appreciate your help.


--
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

Select Case Statement
 

Katie i have to be honest i had forgoten about it will take a look
tomorrow!

Sorry emb1


--
The Code Cage Team

Regards,
The Code Cage Team
http://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

Select Case Statement
 

Hello, Just trying to see if you had been able to take a look at this or
not.

Thanks
Katie


--
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