Change Criteria in Code

H

hotherps

I'm using the following code:

For Each CELL In Range("B9:K20")
If CELL.Value = "PICK" Then
For J = 1 To 8
If CELL.Offset(0, J).Address <> "F5" Then
CELL.Offset(0, J).Value = "PICK"
End If
Next J
End If
Next CELL

As you can see the criteria is if a cell in the range = "Pick".

I want to create a second For next loop that will look at a differen
range of cells C:10 and C11. If C11 > C10 then insert "Pick".

The continue if D11 > D10 etc.

Can't get it to work.
Thank
 
T

Tom Ogilvy

insert Pick where?

for each cell in Range("C10:M10")
if cell.Value > cell.offset(1,0).Value then
cell.offset(0,something).Value = "Pick"
end if
Next
 
H

hotherps

Thanks Tom, sorry about that Pick should go to
For Each CELL In Range("B9:K20")

It should fill in each row B9:K20 until the other condition is met.

Thanks!
 
H

hotherps

Tom was I supposed to replace my original For Next?
I'm getting a "For Next variable already in use"
Thanks

For Each cell In Range("C10:M10")
If cell.Value > cell.Offset(1, 0).Value Then
cell.Offset(0, 2).Value = "Pick"
End If
Next

For Each cell In Range("B9:K20")

'If CELL.Value = "PICK" Then
For J = 1 To 8
If cell.Offset(0, J).Address <> "F5" Then
cell.Offset(0, J).Value = "PICK"
End If
Next J
End If
Next cel
 
T

Tom Ogilvy

Well, you commented out one of your if statements and didn't comment out the
corresponding End if, so that gives an error. I couldn't get the error you
describe. If I uncomment the if statment, it compiles fine:

Sub tester15()
For Each CELL In Range("C10:M10")
If CELL.Value > CELL.Offset(1, 0).Value Then
CELL.Offset(0, 2).Value = "Pick"
End If
Next

For Each CELL In Range("B9:K20")

If CELL.Value = "PICK" Then
For J = 1 To 8
If CELL.Offset(0, J).Address <> "F5" Then
CELL.Offset(0, J).Value = "PICK"
End If
Next J
End If
Next CELL

End Sub

I have no idea what you are doing, so I answered the question the best I
could. What you should actually do to attain the results you desire lies in
the details you have not revealed.
 
H

hotherps

Thanks again Tom,

What I am trying to do is to assign functions for people to work in.
I have a column (A) for their name and then I have a row of cell
(D10:AJ10) that lists the time of day on 20 minute intervals beginin
with 12:00am. I want to insert a function for each cell in the row
There are many possible functions.

Lower on the sheet (C400:C425) Each function is listed. Next to eac
function is the number of hours required to perform the function fo
that day broken out by 1 hour intervals. "So if Pick" is the functio
in C400 i need to look at D400 (12:00am) to see how many hours ar
needed. If the number is > than zero then assign the task Picks to th
employee in the corresponding row up top.
Then loop through the range of tasks D401, D402 etc. Until all task
are 0 or there are no more employees to assign.

I hope that makes sense to you. I'm going to attch a sample.
Thanks so much for your help on this!

Attachment filename: schedule.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=50797
 
Top