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.

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.

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.

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.

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.

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.

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.

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.

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.

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.