Select Case Statement

K

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
 
T

The Code Cage Team

Wow!, i really don't know what you are trying to do there but the selec
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 Tea

Regards,
The Code Cage Team
www.thecodecage.co
 
K

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

butrfly_kis3

I am trying to create an excel spreadsheet that will be in a sens
"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 (labele

straight_time & the year). I would then like it to calculate the tota
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 an
hours
over 8 it needs to calculate it based on the overtime rate for tha
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 g

about writing this out.
It would be nice to have it run in the background and only have
formula in
the necessary columns.
Hopefully this explains what I am trying to do a little clearer
 
S

Simon Lloyd

butrfly_kis3;22920 said:
.....
It would be nice to have it run in the background and only have
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
Click to expand...
workbook so we can see your structure, please make sure there is n
personal or sensitive data in it, all we/anyone here needs is th
structure/layout and some dummy data resembling what you are using, yo
should leave any range names or sheet names the same as this will hel
us help you

To upload a workbook, click reply then add your few words, scroll dow
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 troubl
please use the 'Contact Us' (http://www.thecodecage.com/hesk) form
 
K

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

The Code Cage Team

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

--
The Code Cage Tea

Regards,
The Code Cage Team
www.thecodecage.co
 
B

butrfly_kis3

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/attachment.php?attachmentid=1 |
+-------------------------------------------------------------------+
 
T

The Code Cage Team

Sorry about the upload size thats been sorted now, i cant fathom ou
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 wha
and in which way and how you expect to see the result and where

--
The Code Cage Tea

Regards,
The Code Cage Team
www.thecodecage.co
 
B

butrfly_kis3

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

butrfly_kis3

Hello,

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

butrfly_kis3

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

Thanks
Kati
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Timescalevalue update gives error 1101 0
MACRO RUN random percentage and copy of column values 1
Conditional Addition 5
Determine which variable was used in a calculation. 2
select case simplification request 7
trouble with (complicated) vlookup searching another workbook 2
Sumifs in previous Excel-Versions 3
Development combinations with exclusion of columns 0

Top