refer to a formula for use in a loop

[hotherps]

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",$E34>$E25),$E$24,"")

Through the range of D2:EI17

Thank

 

[Trevor Shuttleworth]

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]

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(D2I2) 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

 

[Trevor Shuttleworth]

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",$E34>$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).Formula = "=""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).Formula = "=""x"""
Range("D2:AB17").Value = Range("D2:AB17").Value
On Error GoTo 0
End Sub


Regards

Trevor

 

[hotherps]

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

 

[Trevor Shuttleworth]

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).Formula = _

"=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).Formula = _

"=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).Formula = _

"=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).Formula = _

"=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).Formula = _

"=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]

That is amazing! I can't believe how fast it runs. I would have though
the only way to do this would be some sort of Loop. I have written
few of those, they work well, but are so slow.

I did'nt get the logic at first and I made a mistake pasting it i
(don't ask me how I did that) but that was good because I had to reall
look at the code to figure it out. So is "Temp" acting as an array? O
does it just hold the value for just one cycle.

After thinking about it some more I'm trying to think of the drawback
of doing it this way. Is it that you can not apply any additional logi
to it, like : Do while F24 > F34 and have F34 driven by a CountI
formula.

Is it just that it processes the question posed once, like once it see
the condition has been met it does'nt stop to evaluate it anymore? I
just performs the remaining parts of the formula.

Thanks again Trevor, you must have put some time into writing th
code. I really appreciate it

 

[hotherps]

Trevor I posted a response but it looks like it was not posted. I jus
had afew questions on the method you write. I'm trying to understan
what the drawbacks of approach like this are. I have writen code befor
that loops through a range and has a simplae parameter to follow and i
takes forever, yours is virtually instant.

The only thing I could think of is that it is faster because it onl
checks the condition once? Where in a loop it checks each cell. woul
that be correct?

And when you set up the Temp reference is that taking the place of a
array?

I also added some code ti put in breaks and lunches, and it stagger
them apart jusy click on the "Loop" button.

Thanks agai

Attachment filename: trevorschedule.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51556

 

[Trevor Shuttleworth]

I saw your first post but I've been out all day so I've not had a chance to
reply.

The code is fast because a) it isn't looping and b) it is using Excel
functions on the worksheet.

The original response/code simply selected the range and put a formula in
each cell. Excel automatically adjusted the formula for each cell ... hence
no effort on my part and all the calculation done in Excel.

The next stage involved putting a slightly modified formula in each BLANK
cell. I found that I either needed to work out where the first blank cell
was and put a suitably adjusted formula in there and then copy it to all the
other blank cells OR start with the original first cell in the range - D2.
But, to do that, I needed to save the value in D2 somewhere; the Temp
variable. This is NOT an array; it only holds the value in cell D2.

So, I save the value of D2 and blank it out. Then I put the new formula in
all the cells in the range. And then I restore the value to D2. In your
original sample data, this works because it calculated the value as "Pick".
So I save "Pick", blank it out and then restore "Pick" ... no problem. BUT,
if the value had originally been blank and the new formula had calculated
the value "Pack", it would be overwritten by a blank ... not quite what was
intended.

Hence the reason for suggesting the alternative approach, inserting a blank
line before the Employee data. It doesn't matter what formula I put in this
new blank row because it will never match your criteria and all the cells in
the row will remain blank. So now I don't need to save and restore the
value in cell D2 and I don't need the Temp variable.

Just to tidy the code up a little, I realised that I could use exactly the
same range for each of the three formulae, hence the end result.

I don't think there is a down side to the approach or the code ... other
than the range and the formulae are hard coded into the VBA. So, whenever
you change the number of employees, modify the time range, increase or
decrease the number of tasks or move the tables, etc., you will have to
adjust the code. That's not a big deal unless you need to make changes
often in which case it could be a pain. However, judging by the sample you
posted you have adjusted most, if not all of these aspects on the worksheet
so you shouldn't have any difficulty.

So, just to clarify: there is NO looping. There is no need for the Temp
variable (which is NOT an array) now you have adopted the latter approach.
All that happens is that the VBA code drops a formula into the BLANK cells;
Excel calculates the values and the code converts them from formulae to
values. This is then repeated with as many formulae as you have tasks.

I would guess that an approach involving looping through all the cells means
that every time you put a formula into a cell, Excel will need to
recalculate and redisplay the worksheet. In this case that's about 560
calculations and screen refreshes. This can be improved by stopping the
screen updating and by switching the calculation off before the code.
Switch them back on again after the code is complete.

I hope this is clear and helps you to understand. It is not always easy to
explain the approach adopted and the way it evolves. Good luck with your
project.

If you want to post your email address, if I have time, I will test an
approach that will be a little more flexible and mail it to you.

Regards

Trevor

 

[hotherps]

That would be great Trevor!
(e-mail address removed)


I think I understand your explanation, I'm going to read it again