Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select case in vba | Excel Discussion (Misc queries) | |||
Select Case | Excel Discussion (Misc queries) | |||
Select case | Excel Discussion (Misc queries) | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Select Case | Excel Discussion (Misc queries) |