Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add one more condition
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add one more condition
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add one more condition
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add one more condition
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add one more condition
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add one more condition
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add one more condition
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 -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add one more condition
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 -- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add one more condition
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 -- Regards, Tom Ogilvy "hotherps " wrote in message ... HI Tom, Thanks so much for your help. I had to change a few things to get the code to run, and it seems to be running fine, the Intersect statement is working also. One part of the functionality changed though: It is disregarding the = and <= in each row. If there is an "x" out between AK:AP it starts to fill the you even though the = or <= has already been met. Don't understand why it stopped working Thanks again --- Message posted from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add one more condition
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if & And condition | Excel Worksheet Functions | |||
lookup with multiple condition, but one condition to satisfy is en | Excel Worksheet Functions | |||
Combine an OR condition with an AND condition | Excel Discussion (Misc queries) | |||
If condition | Excel Discussion (Misc queries) | |||
Condition 1 overules condition 2? | Excel Worksheet Functions |