Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining multiple IF statements
I have 9 different IF statements that need to be put into the same macro.
Some of these conditions are to delete something that meets a criteria, some replace words with something else etc. Will excel 2003 allow the combining of IF statements of different kinds? (the statements do different things and refer to different columns etc)? I've been told i have to use the word IF's rather than IF, i dont have a clue where to start! please help. thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining multiple IF statements
there is no limitation to the number or types of IF statements that can be
put into a macro.. You just have to be smart and make sure there are no conflicts between the IFs For example, if the first IF deletes columns J,K,L then a second IF must cosider two cases. First case, when the first IF did delete the columns and the second case where the first IF did not delete the columns. "Zak" wrote: I have 9 different IF statements that need to be put into the same macro. Some of these conditions are to delete something that meets a criteria, some replace words with something else etc. Will excel 2003 allow the combining of IF statements of different kinds? (the statements do different things and refer to different columns etc)? I've been told i have to use the word IF's rather than IF, i dont have a clue where to start! please help. thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining multiple IF statements
Please can you start me off please? The below are 3 of my conditions, as
evident below the first one asks it to find a word then change the corresponding cell in the next column to a given word. The second and third tells it to to delete certain lines but both refering to different columns.. I am just unsure of the syntax and how i should combine them and specify each time i want it to look at a different column. Please can you combine these 3 so that i can get some sort of idea as to how to word things then i can add the other few in myself. thanks a lot my code: code 1: Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng If r.Value = "Capula" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "Microsoft" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "iSOFT" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "System C" Then r.Offset(0, 1).Value = "AP" End If End If End If End If code 2: Sub dr() mc = "I" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i, mc) = "AMS Subcon" _ Or Cells(i, mc) = "Red Tray" Then Rows(i).delete Next i If Cells(i, mc) = "Microsoft" _ Or Cells(i, mc) = "iSOFT" Then Rows(i).delete End Sub code 3: Sub DeleteRowsbyDate() For Each cll In Range([x1], [x1].End(xlDown)) If IsDate(cll) And cll Now() Then _ cll.EntireRow.delete Next End Sub "Joel" wrote: there is no limitation to the number or types of IF statements that can be put into a macro.. You just have to be smart and make sure there are no conflicts between the IFs For example, if the first IF deletes columns J,K,L then a second IF must cosider two cases. First case, when the first IF did delete the columns and the second case where the first IF did not delete the columns. "Zak" wrote: I have 9 different IF statements that need to be put into the same macro. Some of these conditions are to delete something that meets a criteria, some replace words with something else etc. Will excel 2003 allow the combining of IF statements of different kinds? (the statements do different things and refer to different columns etc)? I've been told i have to use the word IF's rather than IF, i dont have a clue where to start! please help. thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining multiple IF statements
Zak,
I wouldn't do it like that, I'd use select case, you can add addiotinal cases, Try this Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng Select Case r.Value Case "Capula" myvalue = "AP" Case "Microsoft" myvalue = "AP" Case "iSoft" myvalue = "AP" End Select r.Offset(0, 1).Value = myvalue Next End Sub Mike "Zak" wrote: Please can you start me off please? The below are 3 of my conditions, as evident below the first one asks it to find a word then change the corresponding cell in the next column to a given word. The second and third tells it to to delete certain lines but both refering to different columns.. I am just unsure of the syntax and how i should combine them and specify each time i want it to look at a different column. Please can you combine these 3 so that i can get some sort of idea as to how to word things then i can add the other few in myself. thanks a lot my code: code 1: Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng If r.Value = "Capula" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "Microsoft" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "iSOFT" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "System C" Then r.Offset(0, 1).Value = "AP" End If End If End If End If code 2: Sub dr() mc = "I" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i, mc) = "AMS Subcon" _ Or Cells(i, mc) = "Red Tray" Then Rows(i).delete Next i If Cells(i, mc) = "Microsoft" _ Or Cells(i, mc) = "iSOFT" Then Rows(i).delete End Sub code 3: Sub DeleteRowsbyDate() For Each cll In Range([x1], [x1].End(xlDown)) If IsDate(cll) And cll Now() Then _ cll.EntireRow.delete Next End Sub "Joel" wrote: there is no limitation to the number or types of IF statements that can be put into a macro.. You just have to be smart and make sure there are no conflicts between the IFs For example, if the first IF deletes columns J,K,L then a second IF must cosider two cases. First case, when the first IF did delete the columns and the second case where the first IF did not delete the columns. "Zak" wrote: I have 9 different IF statements that need to be put into the same macro. Some of these conditions are to delete something that meets a criteria, some replace words with something else etc. Will excel 2003 allow the combining of IF statements of different kinds? (the statements do different things and refer to different columns etc)? I've been told i have to use the word IF's rather than IF, i dont have a clue where to start! please help. thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining multiple IF statements
The first macro I didn't see any problems. I changed the code to use Select
Case because it is easier to read. The problem with delete rows is yo have to start with the last row and move up otherwise the code doesn't make sense. Using a For Each with a range doesn't ensure that the cells are removed in the correct order. It somehow confuses Excel. Using a RowcCount like I did solves this problem. Your second code should of worked also except the second If statement is outside the FOR loop. I like Select Case so I chnae the code although it is not neessary. The 3rd macro should be written like the first two using a row counter instead of a Range becuase of the delete row. Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng Select Case r.Value Case "Capula", "Microsoft", "iSOFT", "System C" r.Offset(0, 1).Value = "AP" End Select Next r End Sub Sub dr() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" Rows(i).Delete End Select End Sub Sub DeleteRowsbyDate() Lastrow = [x1].End(xlDown).Row For RowCount = Lastrow To 1 Step -1 If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ cll.EntireRow.Delete End If Next End Sub "Zak" wrote: Please can you start me off please? The below are 3 of my conditions, as evident below the first one asks it to find a word then change the corresponding cell in the next column to a given word. The second and third tells it to to delete certain lines but both refering to different columns.. I am just unsure of the syntax and how i should combine them and specify each time i want it to look at a different column. Please can you combine these 3 so that i can get some sort of idea as to how to word things then i can add the other few in myself. thanks a lot my code: code 1: Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng If r.Value = "Capula" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "Microsoft" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "iSOFT" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "System C" Then r.Offset(0, 1).Value = "AP" End If End If End If End If code 2: Sub dr() mc = "I" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i, mc) = "AMS Subcon" _ Or Cells(i, mc) = "Red Tray" Then Rows(i).delete Next i If Cells(i, mc) = "Microsoft" _ Or Cells(i, mc) = "iSOFT" Then Rows(i).delete End Sub code 3: Sub DeleteRowsbyDate() For Each cll In Range([x1], [x1].End(xlDown)) If IsDate(cll) And cll Now() Then _ cll.EntireRow.delete Next End Sub "Joel" wrote: there is no limitation to the number or types of IF statements that can be put into a macro.. You just have to be smart and make sure there are no conflicts between the IFs For example, if the first IF deletes columns J,K,L then a second IF must cosider two cases. First case, when the first IF did delete the columns and the second case where the first IF did not delete the columns. "Zak" wrote: I have 9 different IF statements that need to be put into the same macro. Some of these conditions are to delete something that meets a criteria, some replace words with something else etc. Will excel 2003 allow the combining of IF statements of different kinds? (the statements do different things and refer to different columns etc)? I've been told i have to use the word IF's rather than IF, i dont have a clue where to start! please help. thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining multiple IF statements
I understand what you are saying and even tried inputting the code you gave
but it still doesnt do want i want it to. I want the codes to execute in sequence one after the other instead excel recognises it as 3 different macros as opposed to 1 macro. how do i tell excel that i want them as part of 1 macro?.. i also have an additional few conditions to put in. will excel allow all 9 to run as part of 1 macro or would i need more than 1 macro? thanks so much. "Joel" wrote: The first macro I didn't see any problems. I changed the code to use Select Case because it is easier to read. The problem with delete rows is yo have to start with the last row and move up otherwise the code doesn't make sense. Using a For Each with a range doesn't ensure that the cells are removed in the correct order. It somehow confuses Excel. Using a RowcCount like I did solves this problem. Your second code should of worked also except the second If statement is outside the FOR loop. I like Select Case so I chnae the code although it is not neessary. The 3rd macro should be written like the first two using a row counter instead of a Range becuase of the delete row. Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng Select Case r.Value Case "Capula", "Microsoft", "iSOFT", "System C" r.Offset(0, 1).Value = "AP" End Select Next r End Sub Sub dr() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" Rows(i).Delete End Select End Sub Sub DeleteRowsbyDate() Lastrow = [x1].End(xlDown).Row For RowCount = Lastrow To 1 Step -1 If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ cll.EntireRow.Delete End If Next End Sub "Zak" wrote: Please can you start me off please? The below are 3 of my conditions, as evident below the first one asks it to find a word then change the corresponding cell in the next column to a given word. The second and third tells it to to delete certain lines but both refering to different columns.. I am just unsure of the syntax and how i should combine them and specify each time i want it to look at a different column. Please can you combine these 3 so that i can get some sort of idea as to how to word things then i can add the other few in myself. thanks a lot my code: code 1: Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng If r.Value = "Capula" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "Microsoft" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "iSOFT" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "System C" Then r.Offset(0, 1).Value = "AP" End If End If End If End If code 2: Sub dr() mc = "I" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i, mc) = "AMS Subcon" _ Or Cells(i, mc) = "Red Tray" Then Rows(i).delete Next i If Cells(i, mc) = "Microsoft" _ Or Cells(i, mc) = "iSOFT" Then Rows(i).delete End Sub code 3: Sub DeleteRowsbyDate() For Each cll In Range([x1], [x1].End(xlDown)) If IsDate(cll) And cll Now() Then _ cll.EntireRow.delete Next End Sub "Joel" wrote: there is no limitation to the number or types of IF statements that can be put into a macro.. You just have to be smart and make sure there are no conflicts between the IFs For example, if the first IF deletes columns J,K,L then a second IF must cosider two cases. First case, when the first IF did delete the columns and the second case where the first IF did not delete the columns. "Zak" wrote: I have 9 different IF statements that need to be put into the same macro. Some of these conditions are to delete something that meets a criteria, some replace words with something else etc. Will excel 2003 allow the combining of IF statements of different kinds? (the statements do different things and refer to different columns etc)? I've been told i have to use the word IF's rather than IF, i dont have a clue where to start! please help. thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining multiple IF statements
yes, this is a much better way i will do this.. but this only states if the
range is I, i now want the macro to do exactly the same thing i.e. change the name of something but this time i want it to locate something in column J and replace it with something else in J. example, find "account" in J and change to "others" in J. can this be inserted within the same string of code as below? where do i insert a line or something to mention that i now want it to look in J to do something in J. on the same note, i have a 'delete.rows' code but i want rows deleted in 3 different columns depending on different criteria, how do i stop and tell excel when to look in column B then E then I etc. hope im making sense! example, i want it to find "21 India" in column B and delete. then i want it to find "microsfot" in I and delete that. i suppose i can group them in terms of columns so that all the things that need to be deleted in column B are togther and I are together etc. thanks. "Mike H" wrote: Zak, I wouldn't do it like that, I'd use select case, you can add addiotinal cases, Try this Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng Select Case r.Value Case "Capula" myvalue = "AP" Case "Microsoft" myvalue = "AP" Case "iSoft" myvalue = "AP" End Select r.Offset(0, 1).Value = myvalue Next End Sub Mike "Zak" wrote: Please can you start me off please? The below are 3 of my conditions, as evident below the first one asks it to find a word then change the corresponding cell in the next column to a given word. The second and third tells it to to delete certain lines but both refering to different columns.. I am just unsure of the syntax and how i should combine them and specify each time i want it to look at a different column. Please can you combine these 3 so that i can get some sort of idea as to how to word things then i can add the other few in myself. thanks a lot my code: code 1: Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng If r.Value = "Capula" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "Microsoft" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "iSOFT" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "System C" Then r.Offset(0, 1).Value = "AP" End If End If End If End If code 2: Sub dr() mc = "I" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i, mc) = "AMS Subcon" _ Or Cells(i, mc) = "Red Tray" Then Rows(i).delete Next i If Cells(i, mc) = "Microsoft" _ Or Cells(i, mc) = "iSOFT" Then Rows(i).delete End Sub code 3: Sub DeleteRowsbyDate() For Each cll In Range([x1], [x1].End(xlDown)) If IsDate(cll) And cll Now() Then _ cll.EntireRow.delete Next End Sub "Joel" wrote: there is no limitation to the number or types of IF statements that can be put into a macro.. You just have to be smart and make sure there are no conflicts between the IFs For example, if the first IF deletes columns J,K,L then a second IF must cosider two cases. First case, when the first IF did delete the columns and the second case where the first IF did not delete the columns. "Zak" wrote: I have 9 different IF statements that need to be put into the same macro. Some of these conditions are to delete something that meets a criteria, some replace words with something else etc. Will excel 2003 allow the combining of IF statements of different kinds? (the statements do different things and refer to different columns etc)? I've been told i have to use the word IF's rather than IF, i dont have a clue where to start! please help. thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining multiple IF statements
the problem is when the different IF are not independant. for example the
2nd and 3rd macros delete rows. Look at the new code I wrote below to combine these two macros. I said you have to understand where there may be conflicts between IF conditions and where ther aren't conflicts. Changing data in certain cells probably isn't a conflict. Sub dr() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 DeleteRow = False Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" DeleteRow = True End Select If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ DeleteRow = True End If If DeleteRow = True Then Rows(RowCount).Delete End If Next RowCount End Sub or use an else Sub dr2() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ Rows(RowCount).Delete Else Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" Rows(RowCount).Delete End Select End If Next End Sub "Zak" wrote: I understand what you are saying and even tried inputting the code you gave but it still doesnt do want i want it to. I want the codes to execute in sequence one after the other instead excel recognises it as 3 different macros as opposed to 1 macro. how do i tell excel that i want them as part of 1 macro?.. i also have an additional few conditions to put in. will excel allow all 9 to run as part of 1 macro or would i need more than 1 macro? thanks so much. "Joel" wrote: The first macro I didn't see any problems. I changed the code to use Select Case because it is easier to read. The problem with delete rows is yo have to start with the last row and move up otherwise the code doesn't make sense. Using a For Each with a range doesn't ensure that the cells are removed in the correct order. It somehow confuses Excel. Using a RowcCount like I did solves this problem. Your second code should of worked also except the second If statement is outside the FOR loop. I like Select Case so I chnae the code although it is not neessary. The 3rd macro should be written like the first two using a row counter instead of a Range becuase of the delete row. Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng Select Case r.Value Case "Capula", "Microsoft", "iSOFT", "System C" r.Offset(0, 1).Value = "AP" End Select Next r End Sub Sub dr() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" Rows(i).Delete End Select End Sub Sub DeleteRowsbyDate() Lastrow = [x1].End(xlDown).Row For RowCount = Lastrow To 1 Step -1 If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ cll.EntireRow.Delete End If Next End Sub "Zak" wrote: Please can you start me off please? The below are 3 of my conditions, as evident below the first one asks it to find a word then change the corresponding cell in the next column to a given word. The second and third tells it to to delete certain lines but both refering to different columns.. I am just unsure of the syntax and how i should combine them and specify each time i want it to look at a different column. Please can you combine these 3 so that i can get some sort of idea as to how to word things then i can add the other few in myself. thanks a lot my code: code 1: Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng If r.Value = "Capula" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "Microsoft" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "iSOFT" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "System C" Then r.Offset(0, 1).Value = "AP" End If End If End If End If code 2: Sub dr() mc = "I" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i, mc) = "AMS Subcon" _ Or Cells(i, mc) = "Red Tray" Then Rows(i).delete Next i If Cells(i, mc) = "Microsoft" _ Or Cells(i, mc) = "iSOFT" Then Rows(i).delete End Sub code 3: Sub DeleteRowsbyDate() For Each cll In Range([x1], [x1].End(xlDown)) If IsDate(cll) And cll Now() Then _ cll.EntireRow.delete Next End Sub "Joel" wrote: there is no limitation to the number or types of IF statements that can be put into a macro.. You just have to be smart and make sure there are no conflicts between the IFs For example, if the first IF deletes columns J,K,L then a second IF must cosider two cases. First case, when the first IF did delete the columns and the second case where the first IF did not delete the columns. "Zak" wrote: I have 9 different IF statements that need to be put into the same macro. Some of these conditions are to delete something that meets a criteria, some replace words with something else etc. Will excel 2003 allow the combining of IF statements of different kinds? (the statements do different things and refer to different columns etc)? I've been told i have to use the word IF's rather than IF, i dont have a clue where to start! please help. thanks. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining multiple IF statements
I used the second code that you gave me as that made more sense to me but
only the one part of the code executed, the date condition didnt work! why would that be? Also, within this same macro id like to add many additional statements like i said earlier, how do i keep stating when the column ref changes? for example, within the one you wrote id like to add another delete condition but this time it is something in column B and another thing is column E. thanks. "Joel" wrote: the problem is when the different IF are not independant. for example the 2nd and 3rd macros delete rows. Look at the new code I wrote below to combine these two macros. I said you have to understand where there may be conflicts between IF conditions and where ther aren't conflicts. Changing data in certain cells probably isn't a conflict. Sub dr() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 DeleteRow = False Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" DeleteRow = True End Select If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ DeleteRow = True End If If DeleteRow = True Then Rows(RowCount).Delete End If Next RowCount End Sub or use an else Sub dr2() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ Rows(RowCount).Delete Else Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" Rows(RowCount).Delete End Select End If Next End Sub "Zak" wrote: I understand what you are saying and even tried inputting the code you gave but it still doesnt do want i want it to. I want the codes to execute in sequence one after the other instead excel recognises it as 3 different macros as opposed to 1 macro. how do i tell excel that i want them as part of 1 macro?.. i also have an additional few conditions to put in. will excel allow all 9 to run as part of 1 macro or would i need more than 1 macro? thanks so much. "Joel" wrote: The first macro I didn't see any problems. I changed the code to use Select Case because it is easier to read. The problem with delete rows is yo have to start with the last row and move up otherwise the code doesn't make sense. Using a For Each with a range doesn't ensure that the cells are removed in the correct order. It somehow confuses Excel. Using a RowcCount like I did solves this problem. Your second code should of worked also except the second If statement is outside the FOR loop. I like Select Case so I chnae the code although it is not neessary. The 3rd macro should be written like the first two using a row counter instead of a Range becuase of the delete row. Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng Select Case r.Value Case "Capula", "Microsoft", "iSOFT", "System C" r.Offset(0, 1).Value = "AP" End Select Next r End Sub Sub dr() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" Rows(i).Delete End Select End Sub Sub DeleteRowsbyDate() Lastrow = [x1].End(xlDown).Row For RowCount = Lastrow To 1 Step -1 If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ cll.EntireRow.Delete End If Next End Sub "Zak" wrote: Please can you start me off please? The below are 3 of my conditions, as evident below the first one asks it to find a word then change the corresponding cell in the next column to a given word. The second and third tells it to to delete certain lines but both refering to different columns.. I am just unsure of the syntax and how i should combine them and specify each time i want it to look at a different column. Please can you combine these 3 so that i can get some sort of idea as to how to word things then i can add the other few in myself. thanks a lot my code: code 1: Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng If r.Value = "Capula" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "Microsoft" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "iSOFT" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "System C" Then r.Offset(0, 1).Value = "AP" End If End If End If End If code 2: Sub dr() mc = "I" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i, mc) = "AMS Subcon" _ Or Cells(i, mc) = "Red Tray" Then Rows(i).delete Next i If Cells(i, mc) = "Microsoft" _ Or Cells(i, mc) = "iSOFT" Then Rows(i).delete End Sub code 3: Sub DeleteRowsbyDate() For Each cll In Range([x1], [x1].End(xlDown)) If IsDate(cll) And cll Now() Then _ cll.EntireRow.delete Next End Sub "Joel" wrote: there is no limitation to the number or types of IF statements that can be put into a macro.. You just have to be smart and make sure there are no conflicts between the IFs For example, if the first IF deletes columns J,K,L then a second IF must cosider two cases. First case, when the first IF did delete the columns and the second case where the first IF did not delete the columns. "Zak" wrote: I have 9 different IF statements that need to be put into the same macro. Some of these conditions are to delete something that meets a criteria, some replace words with something else etc. Will excel 2003 allow the combining of IF statements of different kinds? (the statements do different things and refer to different columns etc)? I've been told i have to use the word IF's rather than IF, i dont have a clue where to start! please help. thanks. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining multiple IF statements
Usually date will not work if the cells are not in date format. Highligh one
of the date cells and go to worksheet menu Format - Number and see what format the cells are using (should be highlight in pop up). You can change tthe format of the cells to a data format to get code to work. Ofr From Range("C" & RowCount) to datevalue(Range("C" & RowCount)) It is also possible that none of the dates are after Now() or the rows that are being checked are the wrong rows. The rows are checking the number of rows in column I and the dates are in column C. "Zak" wrote: I used the second code that you gave me as that made more sense to me but only the one part of the code executed, the date condition didnt work! why would that be? Also, within this same macro id like to add many additional statements like i said earlier, how do i keep stating when the column ref changes? for example, within the one you wrote id like to add another delete condition but this time it is something in column B and another thing is column E. thanks. "Joel" wrote: the problem is when the different IF are not independant. for example the 2nd and 3rd macros delete rows. Look at the new code I wrote below to combine these two macros. I said you have to understand where there may be conflicts between IF conditions and where ther aren't conflicts. Changing data in certain cells probably isn't a conflict. Sub dr() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 DeleteRow = False Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" DeleteRow = True End Select If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ DeleteRow = True End If If DeleteRow = True Then Rows(RowCount).Delete End If Next RowCount End Sub or use an else Sub dr2() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ Rows(RowCount).Delete Else Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" Rows(RowCount).Delete End Select End If Next End Sub "Zak" wrote: I understand what you are saying and even tried inputting the code you gave but it still doesnt do want i want it to. I want the codes to execute in sequence one after the other instead excel recognises it as 3 different macros as opposed to 1 macro. how do i tell excel that i want them as part of 1 macro?.. i also have an additional few conditions to put in. will excel allow all 9 to run as part of 1 macro or would i need more than 1 macro? thanks so much. "Joel" wrote: The first macro I didn't see any problems. I changed the code to use Select Case because it is easier to read. The problem with delete rows is yo have to start with the last row and move up otherwise the code doesn't make sense. Using a For Each with a range doesn't ensure that the cells are removed in the correct order. It somehow confuses Excel. Using a RowcCount like I did solves this problem. Your second code should of worked also except the second If statement is outside the FOR loop. I like Select Case so I chnae the code although it is not neessary. The 3rd macro should be written like the first two using a row counter instead of a Range becuase of the delete row. Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng Select Case r.Value Case "Capula", "Microsoft", "iSOFT", "System C" r.Offset(0, 1).Value = "AP" End Select Next r End Sub Sub dr() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" Rows(i).Delete End Select End Sub Sub DeleteRowsbyDate() Lastrow = [x1].End(xlDown).Row For RowCount = Lastrow To 1 Step -1 If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ cll.EntireRow.Delete End If Next End Sub "Zak" wrote: Please can you start me off please? The below are 3 of my conditions, as evident below the first one asks it to find a word then change the corresponding cell in the next column to a given word. The second and third tells it to to delete certain lines but both refering to different columns.. I am just unsure of the syntax and how i should combine them and specify each time i want it to look at a different column. Please can you combine these 3 so that i can get some sort of idea as to how to word things then i can add the other few in myself. thanks a lot my code: code 1: Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng If r.Value = "Capula" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "Microsoft" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "iSOFT" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "System C" Then r.Offset(0, 1).Value = "AP" End If End If End If End If code 2: Sub dr() mc = "I" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i, mc) = "AMS Subcon" _ Or Cells(i, mc) = "Red Tray" Then Rows(i).delete Next i If Cells(i, mc) = "Microsoft" _ Or Cells(i, mc) = "iSOFT" Then Rows(i).delete End Sub code 3: Sub DeleteRowsbyDate() For Each cll In Range([x1], [x1].End(xlDown)) If IsDate(cll) And cll Now() Then _ cll.EntireRow.delete Next End Sub "Joel" wrote: there is no limitation to the number or types of IF statements that can be put into a macro.. You just have to be smart and make sure there are no conflicts between the IFs For example, if the first IF deletes columns J,K,L then a second IF must cosider two cases. First case, when the first IF did delete the columns and the second case where the first IF did not delete the columns. "Zak" wrote: I have 9 different IF statements that need to be put into the same macro. Some of these conditions are to delete something that meets a criteria, some replace words with something else etc. Will excel 2003 allow the combining of IF statements of different kinds? (the statements do different things and refer to different columns etc)? I've been told i have to use the word IF's rather than IF, i dont have a clue where to start! please help. thanks. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining multiple IF statements
That has worked now, i dont know what i was doing wrong before! please can
you refer to my other question as to how to make reference to more IF statements within the same macro that use a differnt sort of condition (that wont be conflicting) and use a different column. I dont really understand the logic behind the code and how you put the "c" for column c where you did and the "I" for column I where you did. if i understood that i would be able to put in new column reference and new conditions but dont know where to start. please, i need some urgent help on this. thanks so much, appreciate all your help. "Joel" wrote: Usually date will not work if the cells are not in date format. Highligh one of the date cells and go to worksheet menu Format - Number and see what format the cells are using (should be highlight in pop up). You can change tthe format of the cells to a data format to get code to work. Ofr From Range("C" & RowCount) to datevalue(Range("C" & RowCount)) It is also possible that none of the dates are after Now() or the rows that are being checked are the wrong rows. The rows are checking the number of rows in column I and the dates are in column C. "Zak" wrote: I used the second code that you gave me as that made more sense to me but only the one part of the code executed, the date condition didnt work! why would that be? Also, within this same macro id like to add many additional statements like i said earlier, how do i keep stating when the column ref changes? for example, within the one you wrote id like to add another delete condition but this time it is something in column B and another thing is column E. thanks. "Joel" wrote: the problem is when the different IF are not independant. for example the 2nd and 3rd macros delete rows. Look at the new code I wrote below to combine these two macros. I said you have to understand where there may be conflicts between IF conditions and where ther aren't conflicts. Changing data in certain cells probably isn't a conflict. Sub dr() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 DeleteRow = False Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" DeleteRow = True End Select If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ DeleteRow = True End If If DeleteRow = True Then Rows(RowCount).Delete End If Next RowCount End Sub or use an else Sub dr2() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ Rows(RowCount).Delete Else Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" Rows(RowCount).Delete End Select End If Next End Sub "Zak" wrote: I understand what you are saying and even tried inputting the code you gave but it still doesnt do want i want it to. I want the codes to execute in sequence one after the other instead excel recognises it as 3 different macros as opposed to 1 macro. how do i tell excel that i want them as part of 1 macro?.. i also have an additional few conditions to put in. will excel allow all 9 to run as part of 1 macro or would i need more than 1 macro? thanks so much. "Joel" wrote: The first macro I didn't see any problems. I changed the code to use Select Case because it is easier to read. The problem with delete rows is yo have to start with the last row and move up otherwise the code doesn't make sense. Using a For Each with a range doesn't ensure that the cells are removed in the correct order. It somehow confuses Excel. Using a RowcCount like I did solves this problem. Your second code should of worked also except the second If statement is outside the FOR loop. I like Select Case so I chnae the code although it is not neessary. The 3rd macro should be written like the first two using a row counter instead of a Range becuase of the delete row. Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng Select Case r.Value Case "Capula", "Microsoft", "iSOFT", "System C" r.Offset(0, 1).Value = "AP" End Select Next r End Sub Sub dr() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" Rows(i).Delete End Select End Sub Sub DeleteRowsbyDate() Lastrow = [x1].End(xlDown).Row For RowCount = Lastrow To 1 Step -1 If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ cll.EntireRow.Delete End If Next End Sub "Zak" wrote: Please can you start me off please? The below are 3 of my conditions, as evident below the first one asks it to find a word then change the corresponding cell in the next column to a given word. The second and third tells it to to delete certain lines but both refering to different columns.. I am just unsure of the syntax and how i should combine them and specify each time i want it to look at a different column. Please can you combine these 3 so that i can get some sort of idea as to how to word things then i can add the other few in myself. thanks a lot my code: code 1: Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng If r.Value = "Capula" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "Microsoft" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "iSOFT" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "System C" Then r.Offset(0, 1).Value = "AP" End If End If End If End If code 2: Sub dr() mc = "I" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i, mc) = "AMS Subcon" _ Or Cells(i, mc) = "Red Tray" Then Rows(i).delete Next i If Cells(i, mc) = "Microsoft" _ Or Cells(i, mc) = "iSOFT" Then Rows(i).delete End Sub code 3: Sub DeleteRowsbyDate() For Each cll In Range([x1], [x1].End(xlDown)) If IsDate(cll) And cll Now() Then _ cll.EntireRow.delete Next End Sub "Joel" wrote: there is no limitation to the number or types of IF statements that can be put into a macro.. You just have to be smart and make sure there are no conflicts between the IFs For example, if the first IF deletes columns J,K,L then a second IF must cosider two cases. First case, when the first IF did delete the columns and the second case where the first IF did not delete the columns. "Zak" wrote: I have 9 different IF statements that need to be put into the same macro. Some of these conditions are to delete something that meets a criteria, some replace words with something else etc. Will excel 2003 allow the combining of IF statements of different kinds? (the statements do different things and refer to different columns etc)? I've been told i have to use the word IF's rather than IF, i dont have a clue where to start! please help. thanks. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining multiple IF statements
First, I copied items from your original code.
I first explain strings and variables Items in double quotes are strings such as "Microsoft". You can replace a string with a vairable. if Range("A1") = "Microsoft" then is equivalent to MyItem = "Microsoft" if Range("A1") = Item then The replacement of "I" with mc in your code is equivalent to the code above. second, you can replace a cell range with a string and a vvariable Range("A23") is equal to MyCol = A Myrow = 23 Range("MyCol & Myrow) or Myrow = 23 Range("A" & Myrow) I can't really fully answer your question when If statements are going to cause conflicts because there are too many cases. Where you had two diffferent case of deleting a row that caused a conflict. For example sub Test() For RowCount = 1 to 100 if Range("A" & RowCount) = "Microsoft" then rows(RowCount).delete end if if Range("A" & RowCount) = "Adobe" then rows(RowCount).delete end if next rowCount end test In the above example if row 10 and 11 both contained Microsoft the code wouldn't work. The 1st If would delete row 10. Now row 11 becomes row 10. the new row 10 would check the 2nd IF for Adobe and then the row would increase. the new row 10 would never get checked for Microsoft. That is why when deleting rows you usally step negatively. You could write the same code correctly like this. I used a variable DeleteRow to indicate when a row n eeded to be deleted. Then performed the delete at the end of the code so I got only one delete sub Test() RowCount = 1 Do while RowCount <= 100 DeleteRow = False if Range("A" & RowCount) = "Microsoft" then end if if Range("A" & RowCount) = "Adobe" then DeleteRow = true end if if DeleteRow = True rows(RowCount).delete else RowCount = RowCount + 1 end if loop end test "Zak" wrote: That has worked now, i dont know what i was doing wrong before! please can you refer to my other question as to how to make reference to more IF statements within the same macro that use a differnt sort of condition (that wont be conflicting) and use a different column. I dont really understand the logic behind the code and how you put the "c" for column c where you did and the "I" for column I where you did. if i understood that i would be able to put in new column reference and new conditions but dont know where to start. please, i need some urgent help on this. thanks so much, appreciate all your help. "Joel" wrote: Usually date will not work if the cells are not in date format. Highligh one of the date cells and go to worksheet menu Format - Number and see what format the cells are using (should be highlight in pop up). You can change tthe format of the cells to a data format to get code to work. Ofr From Range("C" & RowCount) to datevalue(Range("C" & RowCount)) It is also possible that none of the dates are after Now() or the rows that are being checked are the wrong rows. The rows are checking the number of rows in column I and the dates are in column C. "Zak" wrote: I used the second code that you gave me as that made more sense to me but only the one part of the code executed, the date condition didnt work! why would that be? Also, within this same macro id like to add many additional statements like i said earlier, how do i keep stating when the column ref changes? for example, within the one you wrote id like to add another delete condition but this time it is something in column B and another thing is column E. thanks. "Joel" wrote: the problem is when the different IF are not independant. for example the 2nd and 3rd macros delete rows. Look at the new code I wrote below to combine these two macros. I said you have to understand where there may be conflicts between IF conditions and where ther aren't conflicts. Changing data in certain cells probably isn't a conflict. Sub dr() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 DeleteRow = False Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" DeleteRow = True End Select If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ DeleteRow = True End If If DeleteRow = True Then Rows(RowCount).Delete End If Next RowCount End Sub or use an else Sub dr2() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ Rows(RowCount).Delete Else Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" Rows(RowCount).Delete End Select End If Next End Sub "Zak" wrote: I understand what you are saying and even tried inputting the code you gave but it still doesnt do want i want it to. I want the codes to execute in sequence one after the other instead excel recognises it as 3 different macros as opposed to 1 macro. how do i tell excel that i want them as part of 1 macro?.. i also have an additional few conditions to put in. will excel allow all 9 to run as part of 1 macro or would i need more than 1 macro? thanks so much. "Joel" wrote: The first macro I didn't see any problems. I changed the code to use Select Case because it is easier to read. The problem with delete rows is yo have to start with the last row and move up otherwise the code doesn't make sense. Using a For Each with a range doesn't ensure that the cells are removed in the correct order. It somehow confuses Excel. Using a RowcCount like I did solves this problem. Your second code should of worked also except the second If statement is outside the FOR loop. I like Select Case so I chnae the code although it is not neessary. The 3rd macro should be written like the first two using a row counter instead of a Range becuase of the delete row. Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng Select Case r.Value Case "Capula", "Microsoft", "iSOFT", "System C" r.Offset(0, 1).Value = "AP" End Select Next r End Sub Sub dr() mc = "I" For RowCount = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 Select Case Range(mc & RowCount).Value Case "AMS Subcon", "Red Tray", "Microsoft", "iSOFT" Rows(i).Delete End Select End Sub Sub DeleteRowsbyDate() Lastrow = [x1].End(xlDown).Row For RowCount = Lastrow To 1 Step -1 If IsDate(Range("C" & RowCount)) And _ Range("C" & RowCount) Now() Then _ cll.EntireRow.Delete End If Next End Sub "Zak" wrote: Please can you start me off please? The below are 3 of my conditions, as evident below the first one asks it to find a word then change the corresponding cell in the next column to a given word. The second and third tells it to to delete certain lines but both refering to different columns.. I am just unsure of the syntax and how i should combine them and specify each time i want it to look at a different column. Please can you combine these 3 so that i can get some sort of idea as to how to word things then i can add the other few in myself. thanks a lot my code: code 1: Sub Replace1() Dim r As Range Dim srng As Range Set srng = Range("I1", Range("I" & Rows.Count). _ End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues) For Each r In srng If r.Value = "Capula" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "Microsoft" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "iSOFT" Then r.Offset(0, 1).Value = "AP" Else If r.Value = "System C" Then r.Offset(0, 1).Value = "AP" End If End If End If End If code 2: Sub dr() mc = "I" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i, mc) = "AMS Subcon" _ Or Cells(i, mc) = "Red Tray" Then Rows(i).delete Next i If Cells(i, mc) = "Microsoft" _ Or Cells(i, mc) = "iSOFT" Then Rows(i).delete End Sub code 3: Sub DeleteRowsbyDate() For Each cll In Range([x1], [x1].End(xlDown)) If IsDate(cll) And cll Now() Then _ cll.EntireRow.delete Next End Sub "Joel" wrote: there is no limitation to the number or types of IF statements that can be put into a macro.. You just have to be smart and make sure there are no conflicts between the IFs For example, if the first IF deletes columns J,K,L then a second IF must cosider two cases. First case, when the first IF did delete the columns and the second case where the first IF did not delete the columns. "Zak" wrote: I have 9 different IF statements that need to be put into the same macro. Some of these conditions are to delete something that meets a criteria, some replace words with something else etc. Will excel 2003 allow the combining of IF statements of different kinds? (the statements do different things and refer to different columns etc)? I've been told i have to use the word IF's rather than IF, i dont have a clue where to start! please help. thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help on combining two IF statements | Excel Worksheet Functions | |||
Combining 2 IF statements | Excel Discussion (Misc queries) | |||
combining IF and AND statements for multiple columns | Excel Discussion (Misc queries) | |||
Combining IF and multiple SUMIF statements, if A>0 & B is between | Excel Worksheet Functions | |||
Combining IF statements | Excel Programming |