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.

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.

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.

On 12 Jan., 21:17, 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.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi

Sub Replace()
Dim r As Range
Set r = Range("I1", Range("I65536").End(xlUp))
For Each c In r
If c.Value Like "*GTS*" Then c.Offset(0, 1).Value = "GTS"
Next
End Sub

//Per

Hi,

Thanks a lot that worked!

I hope its ok to trouble you for another IF statement, i want to write a
macro that will recognise that column x date is beyond todays date and delete
the rows.

So, if column X date is beyond/greater than todays date (and this date
should the current day) then the rows should be deleted.

Also, can the same macro have different types of statements in it? for
example, delete row statements, IF statements etc? or would these have to be
seperate macros?

thanks in advance.

"Per Jessen" wrote:

On 12 Jan., 21:17, 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.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi

Sub Replace()
Dim r As Range
Set r = Range("I1", Range("I65536").End(xlUp))
For Each c In r
If c.Value Like "*GTS*" Then c.Offset(0, 1).Value = "GTS"
Next
End Sub

//Per

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.

The code your colleague gave did work, it now looks in column range I1:I65536
and finds anything like GTS and replaces it with just GTS.

Please can you help on the date issue:

"i want to write a macro that will recognise that column x date is beyond
todays date and delete the rows.

So, if the date in column X is beyond/greater than todays date (and this date
should be the current day) then the rows should be deleted."

thanks.


"Gord Dibben" wrote:

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.