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.