Need help creating a case statement

[hotherps]

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

 

[Leo Heuser]

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.

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

 

[Bob Phillips]

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]

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

 

[Leo Heuser]

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

 

[hotherps]

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

 

[Bob Phillips]

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]

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

 

[hotherps]

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

 

[Leo Heuser]

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 D2530 used for?
How about employees (e.g. Emp10), who work past midnight?

LeoH

PS
In a lengthy dialogue a name is appreciated

 

[hotherps]

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

 

[Leo Heuser]

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!

 

[hotherps]

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

 

[Leo Heuser]

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