Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
I'm sure there is a better way to do this, but this is all I can come u
with. If you notice below the only thing different between the tw groups of code is: Cell.Offset(0,33) and Cell.Offset(0,34) And j = 0 to 6 changes to k = 7 to 13 Is there a way to use a case statement so I do not have to keep writin the code over and over. I need to continue incrementing the offset, and the for variable quit a few more times. For Each Cell In Range("D3:AJ19") If [D$1] = [$B3] And [D$1] <= [$C3] And _ [E24] = [AK1] And Cell.Offset(0, 33) = 2 Then For j = 0 To 6 Cell.Offset(0, j).Value = [AK$1] Next j If [D$1] = [$B3] And [D$1] <= [$C3] And _ [E24] = [AK1] And Cell.Offset(0, 34) = 2 Then For k = 7 To 13 Cell.Offset(0, k).Value = [AL$1] Next k Thank -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
Try this one (untested):
For Each Cell In Range("D3:AJ19") IF [D$1] = [$B3] And [D$1] <= [$C3] And _ [E24] = [AK1] And Cell.Offset(0, 33+OffsetVal) = 2 Then For j = StartNum To StartNum+6 Cell.Offset(0, j).Value = [AK$1] Next j StartNum = StartNum + 7 OffsetVal = OffsetVal + 1 End if Next Cell I take it, that [AL$1] is supposed to be [AK$1] -- Best Regards Leo Heuser Followup to newsgroup only please. "hotherps " skrev i en meddelelse ... I'm sure there is a better way to do this, but this is all I can come up with. If you notice below the only thing different between the two groups of code is: Cell.Offset(0,33) and Cell.Offset(0,34) And j = 0 to 6 changes to k = 7 to 13 Is there a way to use a case statement so I do not have to keep writing the code over and over. I need to continue incrementing the offset, and the for variable quite a few more times. For Each Cell In Range("D3:AJ19") If [D$1] = [$B3] And [D$1] <= [$C3] And _ [E24] = [AK1] And Cell.Offset(0, 33) = 2 Then For j = 0 To 6 Cell.Offset(0, j).Value = [AK$1] Next j If [D$1] = [$B3] And [D$1] <= [$C3] And _ [E24] = [AK1] And Cell.Offset(0, 34) = 2 Then For k = 7 To 13 Cell.Offset(0, k).Value = [AL$1] Next k Thanks --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
If you want a Case solution, try
For Each Cell In Range("D3:AJ19") Select Case True Case [D$1] = [$B3] And [D$1] <= [$C3] And _ [E24] = [AK1] And Cell.Offset(0, 33) = 2 : For j = 0 To 6 Cell.Offset(0, j).Value = [AK$1] Next j Case [D$1] = [$B3] And [D$1] <= [$C3] And _ [E24] = [AK1] And Cell.Offset(0, 34) = 2 : For k = 7 To 13 Cell.Offset(0, k).Value = [AL$1] Next k End Select Next Cell -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "hotherps " wrote in message ... I'm sure there is a better way to do this, but this is all I can come up with. If you notice below the only thing different between the two groups of code is: Cell.Offset(0,33) and Cell.Offset(0,34) And j = 0 to 6 changes to k = 7 to 13 Is there a way to use a case statement so I do not have to keep writing the code over and over. I need to continue incrementing the offset, and the for variable quite a few more times. For Each Cell In Range("D3:AJ19") If [D$1] = [$B3] And [D$1] <= [$C3] And _ [E24] = [AK1] And Cell.Offset(0, 33) = 2 Then For j = 0 To 6 Cell.Offset(0, j).Value = [AK$1] Next j If [D$1] = [$B3] And [D$1] <= [$C3] And _ [E24] = [AK1] And Cell.Offset(0, 34) = 2 Then For k = 7 To 13 Cell.Offset(0, k).Value = [AL$1] Next k Thanks --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
Thanks Leo,
The code is working somewhat, I'm not sure exactly what is missin though. You were correct about the last cell it is supposed to be AK however the next time the loop runs on that same row, it should the increment to AL, AM etc. On the rows that the code runs on it starts at the proper location, bu for some reason when the same criteria is met it will not run. Can' figure it out. I'm attaching it if you want to take a look. Thanks agai Attachment filename: copy of hotherps7.xls Download attachment: http://www.excelforum.com/attachment.php?postid=55971 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
You're welcome.
Will this one do the job? For Each Cell In Range("D3:AJ19") IF [D$1] = [$B3] And [D$1] <= [$C3] And _ [E24] = [AK1] And Cell.Offset(0, 33+OffsetVal) = 2 Then For j = StartNum To StartNum+6 Cell.Offset(0, j).Value = Range(AK$1).Offset(0,OffsetVal).Value Next j StartNum = StartNum + 7 OffsetVal = OffsetVal + 1 End if Next Cell -- Best Regards Leo Heuser Followup to newsgroup only please. "hotherps " skrev i en meddelelse ... Thanks Leo, The code is working somewhat, I'm not sure exactly what is missing though. You were correct about the last cell it is supposed to be AK, however the next time the loop runs on that same row, it should then increment to AL, AM etc. On the rows that the code runs on it starts at the proper location, but for some reason when the same criteria is met it will not run. Can't figure it out. I'm attaching it if you want to take a look. Thanks again Attachment filename: copy of hotherps7.xls Download attachment: http://www.excelforum.com/attachment.php?postid=559713 --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
Thanks Bob, that is very close to what I'm after, but one strange thin
though. The code seems to ignore the first if statement to some degree In some places it starts exactly where it should, but in others i starts displaying the result in cells that have a lesser value tha [D$1] = [$B3] And [D$1] <= [$C3] There are some cases where D1 is less than B3 but the code start anyway. But the transition to the next value from AK to AL is working well. Thanks -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
Strange, what does the data look like?
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "hotherps " wrote in message ... Thanks Bob, that is very close to what I'm after, but one strange thing though. The code seems to ignore the first if statement to some degree. In some places it starts exactly where it should, but in others it starts displaying the result in cells that have a lesser value than [D$1] = [$B3] And [D$1] <= [$C3] There are some cases where D1 is less than B3 but the code starts anyway. But the transition to the next value from AK to AL is working well. Thanks! --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
I'm attaching the file if you woul dlike to take a look, one tab has
sample of what the finished data might look like. I tried to attach i earlier, but I don't think it went through. Thanks Ji Attachment filename: copy of hotherps7.zip Download attachment: http://www.excelforum.com/attachment.php?postid=56003 -- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
Much Closer, Thanks Leo!
But this is what is happening that should not. As the code reaches the end of the range for each row it should drop one row and start from the begining of the range. Right now it is dropping down but it is starting from where the last row ended. It also increments to the next value, even though that value does not = 2 in the next row. I'll attach the file Attachment filename: copy of hotherps72.zip Download attachment: http://www.excelforum.com/attachment.php?postid=560768 --- Message posted from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
I downloaded hotherps7 and hotherps72 and it looks interesting,
but I can't seem to grasp the logic behind the model. You are welcome to e-mail me for further discussion :-) When are E26:J30 in the sheet "SampledFinishedProduct" used ? Could you explain in everyday language, why e.g. M16 is Ppi. When is a cell "Brk" or "Lun"? What are the times in D25:D30 used for? How about employees (e.g. Emp10), who work past midnight? LeoH PS In a lengthy dialogue a name is appreciated "hotherps " skrev i en meddelelse ... Much Closer, Thanks Leo! But this is what is happening that should not. As the code reaches the end of the range for each row it should drop one row and start from the begining of the range. Right now it is dropping down but it is starting from where the last row ended. It also increments to the next value, even though that value does not = 2 in the next row. I'll attach the file Attachment filename: copy of hotherps72.zip Download attachment: http://www.excelforum.com/attachment.php?postid=560768 --- Message posted from http://www.ExcelForum.com/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
Thanks again Leo, i did not see your e-mail address, you indicated
could e-mail you. Sorry aboout forgetting my name, it is Jim. Let m try to answer your questions in the order you asked them. E:26 J:30 will actualy come from a different linked spreadsheet. Th values are the number of hours required in each task per time period for that day. PPI, Pack etc. are abbreviations of different tasks that can b performed while working on a given shift. This number can, and usuall does change by day. and sometimes might not be needed at all. Brk or Lun, are actually Break and Lunch, I like to try and set break about two hours into a shift then 2 hours later Lunch, and one mor break two hours after that. The code asssigns them and then shuffle them to try and keep as many people working as possible at any give time. That code is basic but it seems to work OK. The times in D25:D30 indicate what time of day the task is required. S in D26, 25 hours of time are needed to perform this task. So in th range above we would need to see 100 15minute blocks populated wit "Mail" to reach the requirement. (There are over 200 employees) There are 24 hour shifts, the model I provided is a condensed versio af a very large spread sheet that is used. You are looking at miniature version of all of the ranges I could zip you a version of what the actual file looks like. I'm jus trying to get the functionality working before applying it to the file it is rather large. The logic goes like this: The workload is driven by hours required per task, per day, per hour Then the task has to get matched to an employee who is working (Betwee Col B (Shift Start) and C (Shift End)). The employee must also b trained to perform that task Range AK3:AP19 and "x" indicates th person is trained in this task. So the code has to assign tasks base on the number of hours needed and the number of employees that can d the task and are working at that time. I hope that makes sense to you. If the code fails i.e. not enough employees to do the task, it shoul just move on to the next task, those will be entered manually. Please forward anymore questions, I'm curious to get your opinion o the whole model, maybe you have a better suggestion? Thanks so much, Ji -- Message posted from http://www.ExcelForum.com |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
Hi Jim
I believe, I understand most of the model by now. At least I understand enough to see, that this isn't just a case of "try this or try that". IMHO it's a job for a consultant, so even if it looks intriguing, I gracefully decline :-) It will be too time consuming. Sorry! -- Best Regards Leo Heuser Followup to newsgroup only please. "hotherps " skrev i en meddelelse ... Thanks again Leo, i did not see your e-mail address, you indicated I could e-mail you. Sorry aboout forgetting my name, it is Jim. Let me try to answer your questions in the order you asked them. E:26 J:30 will actualy come from a different linked spreadsheet. The values are the number of hours required in each task per time period, for that day. PPI, Pack etc. are abbreviations of different tasks that can be performed while working on a given shift. This number can, and usually does change by day. and sometimes might not be needed at all. Brk or Lun, are actually Break and Lunch, I like to try and set breaks about two hours into a shift then 2 hours later Lunch, and one more break two hours after that. The code asssigns them and then shuffles them to try and keep as many people working as possible at any given time. That code is basic but it seems to work OK. The times in D25:D30 indicate what time of day the task is required. So in D26, 25 hours of time are needed to perform this task. So in the range above we would need to see 100 15minute blocks populated with "Mail" to reach the requirement. (There are over 200 employees) There are 24 hour shifts, the model I provided is a condensed version af a very large spread sheet that is used. You are looking at a miniature version of all of the ranges I could zip you a version of what the actual file looks like. I'm just trying to get the functionality working before applying it to the file, it is rather large. The logic goes like this: The workload is driven by hours required per task, per day, per hour. Then the task has to get matched to an employee who is working (Between Col B (Shift Start) and C (Shift End)). The employee must also be trained to perform that task Range AK3:AP19 and "x" indicates the person is trained in this task. So the code has to assign tasks based on the number of hours needed and the number of employees that can do the task and are working at that time. I hope that makes sense to you. If the code fails i.e. not enough employees to do the task, it should just move on to the next task, those will be entered manually. Please forward anymore questions, I'm curious to get your opinion on the whole model, maybe you have a better suggestion? Thanks so much, Jim --- Message posted from http://www.ExcelForum.com/ |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
No Problem Leo, I understand I know I have a lot of work a head of me
But maybe you could help me with one piece of the puzzle. The las version of code that you sent was definitly on the right track. The on thing I could not figure out is why does the code start at the sam point it left on the previous row. It should go back to the begining o the next row at the start time. But instead it drops down a row as i it is continuing from the previous row. I do appreciate your help, thanks again ji -- Message posted from http://www.ExcelForum.com |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help creating a case statement
Maybe something like this:
For Each Cell In Range("D3:AJ19") IF [D$1] = [$B3] And [D$1] <= [$C3] And _ [E24] = [AK1] And Cell.Offset(0, 33+OffsetVal) = 2 Then If Cell.Column = 4 Then StartNum = 0 OffsetVal = 0 End If For j = StartNum To StartNum+6 Cell.Offset(0, j).Value = Range(AK$1).Offset(0,OffsetVal).Value Next j StartNum = StartNum + 7 OffsetVal = OffsetVal + 1 End if Next Cell This is my last guess, Jim :-) -- Best Regards Leo Heuser Followup to newsgroup only please. "hotherps " skrev i en meddelelse ... No Problem Leo, I understand I know I have a lot of work a head of me. But maybe you could help me with one piece of the puzzle. The last version of code that you sent was definitly on the right track. The one thing I could not figure out is why does the code start at the same point it left on the previous row. It should go back to the begining of the next row at the start time. But instead it drops down a row as if it is continuing from the previous row. I do appreciate your help, thanks again jim --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Case Statement | Excel Discussion (Misc queries) | |||
IF STATMENT OR CASE STATEMENT | Excel Discussion (Misc queries) | |||
Anyone actually get Case statement to work ... ? | Excel Worksheet Functions | |||
CASE statement equivalent | Excel Worksheet Functions | |||
Case statement | Excel Programming |