Add one more condition

H

hotherps

I'm using the following code:



For Each Cell In Range("D3:AJ19")
If Range("E25").Value >= Range("G22") And Cell.Value > "" Then
Cell.Value = Range("AO1")

This works well, but I want to add another condition.

I 'll write in pseudo code

if col AK on the active row = "x" then return the value in AK1
continue to do this for the following range of columns AK:AP

Thank
 
T

Tom Ogilvy

Dim rng as Range
For Each Cell In Range("D3:AJ19")
If Range("E25").Value >= Range("G22") And Cell.Value > "" Then
set rng = Nothing
for each cell1 in Intersect(Range("AK:AP"),Cell.EntireRow)
if cell1 = "x" then
' not sure what you mean by return or where to return it to.
set rng = cells(1,cell1.column)
Exit for
end if
Next cell1
if not rng is nothing then
Cell.Value = rng.Value
End if
Next cell
 
H

hotherps

Thanks Tom, I'm still having the problem though. Let me show you th
whole routine :

For Each Cell In Range("D3:AJ19")

If Range("E25").Value >= Range("G22") And Cell.Value > "" Then
Cell.Value = Range("AO1")
If Range("F25").Value >= Range("H22") And Cell.Value > "" Then
Cell.Value = Range("AN1")
If Range("G25").Value >= Range("I22") And Cell.Value > "" Then
Cell.Value = Range("AM1")
If Range("H25").Value >= Range("J22") And Cell.Value > "" Then
Cell.Value = Range("AL1")
If Range("I25").Value >= Range("K22") And Cell.Value > "" Then
Cell.Value = Range("AK1")
End If
End If
End If
End If
End If

Next
On Error GoTo 0

I'm not quite sure where, or how to use the "Intersect" code. I'v
never used it before.

So for example on the first row where the cell AO1 is referenced I onl
want the cell value to be AO1 if the cell AO3 = x
same thing for AO4 etc.

Thanks!!
Ji
 
T

Tom Ogilvy

Well, you wouldn't use the intersect code - what you show now is not what I
understood you to explain.

For Each Cell In Range("D3:AJ19")

If Range("E25").Value >= Range("G22") And Cell.Value > "" and
Cells(Cell.Row,"A0") = "x" Then _
Cell.Value = Range("AO1")
End If
If Range("F25").Value >= Range("H22") And Cell.Value > "" and
Cells(Cell.Row,"AN") = "x" Then _
Cell.Value = Range("AN1")
End If

I doubt you want the construct you have presented, with nested If
statements, but if so, then just use the additional And statement from the
above.

You should be able to follow the pattern.
 
H

hotherps

Thanks Tom, sorry for the poor explanation.
But I think you had the right approach last time. What I want to do i
check to see if AO = x, if it does continue. If not got to the nex
cell in the range AK:AP on the same row.

Is that what the Intersect code is for?

Thanks again

I also agree with you on my whole approach, I guess a case statemen
would be more appropriate
 
T

Tom Ogilvy

For Each Cell In Range("D3:AJ19")

If Range("E25").Value >= Range("G22") And Cell.Value > "" Then
if Cells(cell.row,"AO")= "x" then
cell.Value = Range("AO1").Value
else
for each cell1 in Intersect(Range("AK:AP"),Cell.EntireRow)
if cell1 = "x" then
cell.Value = cells(1,cell1.column)
Exit for
end if
Next cell1
End if
End if

If Range("F25").Value >= Range("H22") And Cell.Value > "" Then
if Cells(cell.row,"AN")= "x" then
cell.Value = Range("AN1").Value
else
for each cell1 in Intersect(Range("AK:AP"),Cell.EntireRow)
if cell1 = "x" then
cell.Value = cells(1,cell1.column)
Exit for
end if
Next cell1
End if
End if
 
H

hotherps

HI Tom, Thanks so much for your help. I had to change a few things t
get the code to run, and it seems to be running fine, the Intersec
statement is working also.

One part of the functionality changed though:

It is disregarding the >= and <= in each row. If there is an "x" ou
between AK:AP it starts to fill the you even though the >= or <= ha
already been met.

Don't understand why it stopped working

Thanks agai
 
H

hotherps

I had a mistake in the code, it is working fine. I ended up doing i
like this:
For Each Cell In Range("D3:AJ24")

If Range("I29").Value >= Range("I26") And Cell.Value > "" An
Cells(Cell.Row, "AO") = "x" Then _
Cell.Value = Range("AO1")

If Range("H29").Value >= Range("H26") And Cell.Value > "" An
Cells(Cell.Row, "AN") = "x" Then _
Cell.Value = Range("AN1")

If Range("G29").Value >= Range("G26") And Cell.Value > "" An
Cells(Cell.Row, "AM") = "x" Then _
Cell.Value = Range("AM1")

If Range("E29").Value >= Range("E26") And Cell.Value > "" An
Cells(Cell.Row, "AK") = "x" Then _
Cell.Value = Range("AK1")

If Range("F29").Value >= Range("F26") And Cell.Value > "" An
Cells(Cell.Row, "AL") = "x" Then
Cell.Value = Range("AL1")

End If
Next
On Error GoTo 0

From what I have tested it seems to be working, thanks again for you
help!!
Ji
 
T

Tom Ogilvy

sounds like you need toconstruct your conditions in an

if conditions then


Elseif conditions then



elseif conditions then


Else

End if

Rather than
if conditions then

End if
if conditions then

End if
if conditions then

End if
 
H

hotherps

Thanks again Tom,
I got it working and combined with some other code for this project i
seems to be working well. I'll attach it in case you would like to se
it run. Could not have done it without you!

Still not finished but getting closer each day.

Go to the Task Allocation tab and press the "Closest Yet" button

and Thanks again
Ji

Attachment filename: copy of hotherps7.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=56421
 
Top