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

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

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

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy

"hotherps " wrote in message
...
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

Thanks


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

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

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

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.

--
Regards,
Tom Ogilvy

"hotherps " wrote in message
...
Thanks Tom, I'm still having the problem though. Let me show you the
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've
never used it before.

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

Thanks!!
Jim


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

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

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

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

--
Regards,
Tom Ogilvy


"hotherps " wrote in message
...
Thanks Tom, sorry for the poor explanation.
But I think you had the right approach last time. What I want to do is
check to see if AO = x, if it does continue. If not got to the next
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 statement
would be more appropriate?


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