Hopefully simple Select Case error
 

Can anybody help explain why this part of my Select Case statement won't
work? First time using Select Case so please excuse me if this is
glaringly obvious!

Case "Criteria 1" And _
Cell.Offset(0, 1).Valuelike "*AdditionalCriteria*"

Case "Criteria 2" And _
Cell.Offset(0, 1).Value Like "Additional Criteria"

Thanks,

Adrian



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Hopefully simple Select Case error
 

Wrong syntax...
Use a variable, say, crtr, to hold your case criterion
value, and then:

SELECT CASE crtr
CASE "Criteria 1"
Cell.Offset(0, 1).Valuelike "*AdditionalCriteria*"
CASE "Criteria 2"
Cell.Offset(0, 1).Value Like "Additional Criteria"
CASE ....
action....
CASE ELSE
action if none of the above satisfied / Optional
End SELECT

Nikos Y.
-----Original Message-----
Can anybody help explain why this part of my Select Case
statement won't
work? First time using Select Case so please excuse me if
this is
glaringly obvious!

Case "Criteria 1" And _
Cell.Offset(0, 1).Valuelike "*AdditionalCriteria*"

Case "Criteria 2" And _
Cell.Offset(0, 1).Value Like "Additional Criteria"

Thanks,

Adrian



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

.

Hopefully simple Select Case error
 

If you want all the conditions met as you showed in your previous IF
statement, then select case would be the wrong construct. Select case would
be appropriate if you had

if criteria1 or criteria2 or criteria3

Your code was
if critiera1 and criteria1 and criteria3

Select case is used to do a specific action based on which of the critieria
met - you original required all criteria to be met before doing one thing.

--
Regards,
Tom Ogilvy

"Kobayashi" wrote in message
...
Can anybody help explain why this part of my Select Case statement won't
work? First time using Select Case so please excuse me if this is
glaringly obvious!

Case "Criteria 1" And _
Cell.Offset(0, 1).Valuelike "*AdditionalCriteria*"

Case "Criteria 2" And _
Cell.Offset(0, 1).Value Like "Additional Criteria"

Thanks,

Adrian



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Hopefully simple Select Case error
 

Tom,

Thanks for replying, again.

You're right, I did initially have many If conditions. And, thanks to
your help I got it working. And, when they were all "Criteria..." and
"Critieria..." it was fine. But I then ran into difficulties when I
needed to ascertain additional criteria for just one of the listed
criteria! I got into a mess and just couldn't work it out so I thought
I'd try the Select case approach. At the time I didn't realise, even
after searching this site and the web, that this is not the best option
if you wish to use 'AND'!

I finally got it working by using the Select Case procedure and then
looping through all the cells again with an IF function. This clearly
isn't the best way to do it! I then have to loop through the cells
again for my next procedure due to my inexperience!

Thus, to check 1000 rows of data for one column and delete the rows
that do not meet the criteria to leave an amount of 200 rows, then
assign a simple value such as a name into another column for the
remaining 200 rows has taken me about 6 very long sub procedures to
create, and three loops through the same range! ;-(
It finally works but I know the code will be a nightmare to maintain
and it also takes nearly 2.5 minutes to run!

Regards,

Adrian



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/