Zak

You cannot say "if an entire range or column is like GTS" do something.

You have to look in the cells of that range for the GTS

And you don't want to look in the entire column, just the used part of that
column.

Option Compare Text
Sub test()
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 Like "GTS" Then
r.Offset(0, 1).Value = "GTS"
Else
If r.Value Like "*GIS*" Then
r.Offset(0, 1).Value = "GIS"
End If
End If
Next
End Sub

I think you should be able to work out the "hedra" macro from the above.


Gord Dibben MS Excel MVP

On Sat, 12 Jan 2008 12:17:00 -0800, Zak wrote:

Hi,

i tried this but it still doesnt work. please help. thanks.

Dim r As Range
If Range("I:I") Like "GTS" Then
Range("J:J") = "GTS"
Else
If Range("I:I") = "*GIS*" Then
Range("J:J") = "GIS"
End If
End If

End Sub

Also tried this for something slightly different:

Range (“I:I”).select
If = “hedra” then
Range(“J:J”) = “Alliance partner”
End if
End sub

Its just saying that if something in column I = 'hedra' then the
corresponding cell in column J should change to 'Alliance partner'. how is
this done?

This is all to be done in the same macro. So in the same macro code i will
have around 9 different statments - things for it to do, which include
deleting rows, replacing things etc. will the same macro allow numerous
statements like the above? how should i put them in sequence?

thanks alot
"FSt1" wrote:

hi
if statements
Dim r As Range
If r.Value Like "*GTS*" Then
r.Value = "GTS"
Else
If r.Value = "this" Then
r.Value = "that"
End If
End If

as to the second part, you didn't profide enought info for a reply other
than to say perhaps a loop with some if statement to id what needs to be done.

regards
FSt1

"Zak" wrote:

How do i put in IF statements in macros? i know what i want the macro to do
but cant seem to get the terminology correct, also can i put in numerous IF
statements within the same macro or do i need to create seperate modules?

an example: i want the macro to recognise that IF a cell begins with the
word GTS then it should replace it so it is only GTS. i also want to have IF
statments that say IF something = this then it should change to this. or IF
something = this then it should copy this or paste that.

i know what i want but dont know how to get it.

also, on a different note i am trying to create a macro that will build a
report based on a big spreadsheet. what is the best way to get around this as
at the moment i am just using the record macro option to record while i copy
and paste, hide etc cells to view just what is required for the report! then
format it to look nice, is there a better way to do this?

thanks in advance.