Is there a way to loop through a range of cells referring to a formul
on the active sheet? My formula works but I need it to continue t
evalute cells as it goes through the loop.

I do not want to use the formula directly in the cells because ther
are too many cells in the loop

I want to loop this formula :

=IF(AND(D$1=$B2,D$1<=$C2,$E$24=$AK$1,$AK2="X",$E3 4$E25),$E$24,"")

Through the range of D2:EI17

Thank

--
Message posted from http://www.ExcelForum.com

I'm not really sure what it is you are trying to do. I guess from the fact
that your previous post has had no replies (yet) that others are in the same
position. What exactly do you mean by "looping through a range of cells
referring to a formula on the active sheet" ?

Perhaps it would help if you gave examples of what you want evaluating and
where the answer should go. What formula do you want generating in cell D2?
D3? D4? ... EI1? ... EI17?

Do you want the formula in cell D17 to be:
=IF(AND(D$1=$B17,D$1<=$C17,$E$24=$AK$1,$AK17="X", $E49$E40),$E$24,"")

And in EI17
=IF(AND(EI$1=$B17,EI$1<=$C17,$E$24=$AK$1,$AK17="X ",$E49$E40),$E$24,"")

If so, you could just drag the fill handle down and copy the formulae.

Do you want code to generate something somewhere ?

Not sure if I'm missing something but, as I say, I don't really understand
the question.

Regards

Trevor


"hotherps " wrote in message
...
Is there a way to loop through a range of cells referring to a formula
on the active sheet? My formula works but I need it to continue to
evalute cells as it goes through the loop.

I do not want to use the formula directly in the cells because there
are too many cells in the loop

I want to loop this formula :

=IF(AND(D$1=$B2,D$1<=$C2,$E$24=$AK$1,$AK2="X",$E3 4$E25),$E$24,"")

Through the range of D2:EI17

Thanks


---
Message posted from http://www.ExcelForum.com/

Yes Trevor i could have been more specific but as you noticed, I'm no
getting any replies so I'm kind of giving up on the idea, but here i
how it goes.

To answer your question, Yes I want that formula evaluated for eac
cell in the row, and for the rest of the range for that matter. But t
copy it in every cell would make the file huge. The logic behind i
is:

If an employee's (A) startTime(B) and EndTime(C) is between the time o
day Range(D1:EI1) and the employee is trained in that task(AK1 = "x")
and the task requires hours(E25 0)

Fill in the task name(E24) in each cell of the range the origina
range(D2:DI2) after this row is populated drop down to the next row an
do the same thing until the hours required(E25 = 0)
Then move to the next column (E26) and do the same thing and so on...

Thank

Attachment filename: schedulesample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51235
--
Message posted from http://www.ExcelForum.com

OK, try this:

Sub hotherpsTest()
Range("D2:AB17").Formula = _

"=IF(AND(D$1=$B2,D$1<=$C2,$E$24=$AC$1,$AC2=""X"", $E34$E25),$E$24,"""")"
Range("D2:AB17").Value = Range("D2:AB17").Value
End Sub

It generates formulae from:
=IF(AND(D$1=$B2,D$1<=$C2,$E$24=$AC$1,$AC2="X",$E3 4$E25),$E$24,"")
to:
=IF(AND(AB$1=$B17,AB$1<=$C17,$E$24=$AC$1,$AC17="X ",$E49$E40),$E$24,"")

Then it converts the formulae to values. Voila.

However, I don't know if it gives the answer you expect.

D2 to AA2 = "Pick"
G3 to O3 = "Pick"
D4 to R4 = "Pick"
No "Packs" or "Alps"

Maybe you then need another routine to fill the blanks with another formula
?

You could use something like:

Sub hotherpsTest2()
On Error Resume Next
Range("D2:AB17").SpecialCells(xlCellTypeBlanks).Fo rmula = "=""x"""
On Error GoTo 0
End Sub

So combining the two:

Sub hotherpsTest3()
On Error Resume Next
Range("D2:AB17").Formula = _

"=IF(AND(D$1=$B2,D$1<=$C2,$E$24=$AC$1,$AC2=""X"", $E34$E25),$E$24,"""")"
Range("D2:AB17").Value = Range("D2:AB17").Value
Range("D2:AB17").SpecialCells(xlCellTypeBlanks).Fo rmula = "=""x"""
Range("D2:AB17").Value = Range("D2:AB17").Value
On Error GoTo 0
End Sub


Regards

Trevor


"hotherps " wrote in message
...
Yes Trevor i could have been more specific but as you noticed, I'm not
getting any replies so I'm kind of giving up on the idea, but here is
how it goes.

To answer your question, Yes I want that formula evaluated for each
cell in the row, and for the rest of the range for that matter. But to
copy it in every cell would make the file huge. The logic behind it
is:

If an employee's (A) startTime(B) and EndTime(C) is between the time of
day Range(D1:EI1) and the employee is trained in that task(AK1 = "x")
and the task requires hours(E25 0)

Fill in the task name(E24) in each cell of the range the original
range(D2:DI2) after this row is populated drop down to the next row and
do the same thing until the hours required(E25 = 0)
Then move to the next column (E26) and do the same thing and so on...

Thanks

Attachment filename: schedulesample.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=512357
---
Message posted from http://www.ExcelForum.com/

That worked great Trevor! It assigns the task exactly where it should,
had to change the formula slightly to:

=IF(AND(D$1=$B2,D$1<=$C2,$E$24=$AK$1,$AK2=""X"",$ E$34$E$25),$E$24,""""

But let me ask you this, how would I go about advancing to the othe
tasks I need to assign? Is there a way to have the formula change t
the next tasks label after the first task is satisfied. I could line u
all of the tasks reuired and loop through each?



Or would that be too difficult?

Thanks so much for your help

Attachment filename: trevorschedule.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51419
--
Message posted from http://www.ExcelForum.com

You could try this:

Sub hotherpsTest5()
'
"=IF(AND(D$1=$B2,D$1<=$C2,$E$24=$AK$1,$AK2=""X"", $E$34$E$25),$E$24,"""")"
'
"=IF(AND(D$1=$B2,D$1<=$C2,$F$24=$AL$1,$AL2=""X"", $F$34$F$25),$F$24,"""")"
'
"=IF(AND(D$1=$B2,D$1<=$C2,$G$24=$AM$1,$AM2=""X"", $G$34$G$25),$G$24,"""")"

Dim Temp As String ' temporary storage area for "base" cell

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next

Range("D2:AJ17").Formula = _

"=IF(AND(D$1=$B2,D$1<=$C2,$E$24=$AK$1,$AK2=""X"", $E$34$E$25),$E$24,"""")"
Range("D2:AJ17").Value = Range("D2:AJ17").Value ' convert formulae to values

Temp = Range("D2") ' store "base" cell
Range("D2") = "" ' clear "base" cell
Range("D2:AJ17").SpecialCells(xlCellTypeBlanks).Fo rmula = _

"=IF(AND(D$1=$B2,D$1<=$C2,$F$24=$AL$1,$AL2=""X"", $F$34$F$25),$F$24,"""")"
Range("D2") = Temp ' restore "base" cell
Range("D2:AJ17").Value = Range("D2:AJ17").Value ' convert formulae to values

Temp = Range("D2") ' store "base" cell
Range("D2") = "" ' clear "base" cell
Range("D2:AJ17").SpecialCells(xlCellTypeBlanks).Fo rmula = _

"=IF(AND(D$1=$B2,D$1<=$C2,$G$24=$AM$1,$AM2=""X"", $G$34$G$25),$G$24,"""")"
Range("D2") = Temp ' restore "base" cell
Range("D2:AJ17").Value = Range("D2:AJ17").Value ' convert formulae to values

On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

I need to clear the "base" cell (D2) in order to select the empty cells in
the range and drop the "sample" formula in. I then restore the value to
cell D2 in order to ensure the second and third calculations reflect the
original value. I'm not 100% confident in this approach but it generates
Picks and Packs, not Alps (whatever that is).

It might be safer to insert a blank line before the Employee data and
include that in the range. This new row 2 would never meet the formulae
conditions so should always stay blank and not interfere with any
calculations. You could delete Row 23 so that the ranges in the code don't
all have to change. The code for this would then look like:

Sub hotherpsTest6()
' note: needs a new blank row to be inserted at row 2 before the employee
data
' AND row 23 deleting to compensate.
' the range also needs extending to include row 18
'
"=IF(AND(D$1=$B2,D$1<=$C2,$E$24=$AK$1,$AK2=""X"", $E$34$E$25),$E$24,"""")"
'
"=IF(AND(D$1=$B2,D$1<=$C2,$F$24=$AL$1,$AL2=""X"", $F$34$F$25),$F$24,"""")"
'
"=IF(AND(D$1=$B2,D$1<=$C2,$G$24=$AM$1,$AM2=""X"", $G$34$G$25),$G$24,"""")"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Range("D2:AJ18").SpecialCells(xlCellTypeBlanks).Fo rmula = _

"=IF(AND(D$1=$B2,D$1<=$C2,$E$24=$AK$1,$AK2=""X"", $E$34$E$25),$E$24,"""")"
Range("D2:AJ18").Value = Range("D2:AJ18").Value ' convert formulae to values
Range("D2:AJ18").SpecialCells(xlCellTypeBlanks).Fo rmula = _

"=IF(AND(D$1=$B2,D$1<=$C2,$F$24=$AL$1,$AL2=""X"", $F$34$F$25),$F$24,"""")"
Range("D2:AJ18").Value = Range("D2:AJ18").Value ' convert formulae to values
Range("D2:AJ18").SpecialCells(xlCellTypeBlanks).Fo rmula = _

"=IF(AND(D$1=$B2,D$1<=$C2,$G$24=$AM$1,$AM2=""X"", $G$34$G$25),$G$24,"""")"
Range("D2:AJ18").Value = Range("D2:AJ18").Value ' convert formulae to values
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

This is also a bit neater ... still seems to work which is good

One disadvantage with all these options is that the range will need manually
adjusting if you add employees or move the tables at the bottom ... but one
step at a time

Regards

Trevor


"hotherps " wrote in message
...
That worked great Trevor! It assigns the task exactly where it should, I
had to change the formula slightly to:

=IF(AND(D$1=$B2,D$1<=$C2,$E$24=$AK$1,$AK2=""X"",$ E$34$E$25),$E$24,""""

But let me ask you this, how would I go about advancing to the other
tasks I need to assign? Is there a way to have the formula change to
the next tasks label after the first task is satisfied. I could line up
all of the tasks reuired and loop through each?



Or would that be too difficult?

Thanks so much for your help!

Attachment filename: trevorschedule.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=514197
---
Message posted from http://www.ExcelForum.com/