IF statements in Macros

[Zak]

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.

 

[Mike H]

Zak,

A bit short on details so just guessing this might get you goung in the
right direction.

Sub sonic()
Dim MyRange As Range
Set MyRange = Range("a1:a10")
For Each c In MyRange
If Left(c.Value, 3) = "GTS" Then
'do this
ElseIf Left(c.Value, 3) = "GTS" Then
'do that
Else
'do the other
End If
Next
End Sub

Mike

 

[Zak]

Hi,

I tried the macro but it comes up with some run time error. I am very new to
this so am experiencing a huge amount of stress because nothing seems to work!

your code:

Sub sonic()
Dim MyRange As Range
Set MyRange = Range("a1:a10")
For Each c In MyRange
If Left(c.Value, 3) = "GTS" Then
'do this
ElseIf Left(c.Value, 3) = "GTS" Then
'do that
Else
'do the other
End If

when it says 'for each c in my range' what do you mean? or am i supposed to
change that to something? and when it says things like 'if left', what does
this mean? i am just trying to understanding things so that i am able to do
them. and when you say 'do this' obviously i put what i want the code to do
in there but how should i word it? should i just say 'replace with word GTS'?
or if i want something deleted should i just say 'delete'? And i also want to
specify a date range within the macro so that it recognises all items that
are newer than todays date and automatically deletes them.

to provide you with more info, my spreadsheet has a lot of things in it that
need to be deleted or renamed etc before i can start to work on it. this is
why i want to automate this process as it takes me a while to get through
nearly 2000 records!

please help!

 

[Mike H]

Right click the sheet tab, view code and paste it in there

Mike

 

[GB]

At this moment, I can only answer the first question, the second would
require me to spend some time that I unfortunately can not offer at this
moment:

If statements are a part of a subroutine or a function
so need to have something like:
public sub XYZ()
or
public function XYZ()
or substitute private for public as desired

then after that statement and before a corresponding end (I.e., end sub or
end function)

you can use if statements like this:

if X = Y then

end if

The above is a single if statement that has a clear start and clear end.

In the situation you are discussing where you are looking at the first few
characters of a string for your replacement then you could do something like
this:

if X = Y then
'Do the comparisons/replacements desired
elseif X = Z then
'Do the comparisons/replacements desired
elseif X = AA then
'Do the comparisons/replacements desired
end if

As for the identification that a string begins with desired text then you
could do something like:

if left(LongString,len(ShortString)) = ShortString then

end if

or you may have to use a strcomp function such as:

if strcomp(left(LongString, len(ShortString)), ShortString) = 0 then

end if

the = 0 means that both are equal to each other. I don't recall which is
the case but a positive number means either that the first is greater than
the second or it may mean less than, and a negative number means the reverse.
Text comparisons include case, so if you the replacement of abc with xyz is
acceptable if it starts with ABC and would be replaced by xyz then you can
use the UCASE() or LCASE() function as desired for comparison:

if strcomp(UCASE(left(LongString, len(ShortString))), UCASE(ShortString)) =
0 then

end if