Manuel Cavero

New Member
Joined
Feb 17, 2018
Messages
26
Good morning or afteernooon to everyone:

I've this little piece of VBA code that it's not working:

Code:
Select Case PAc_ALA[INDENT]Case Is <> 5, Is <> 10, Is <> 15, Is <> 20, Is <> 25, Is <> 30, Is <> 35, Is <> 40[/INDENT]
[INDENT]       Do whatever[/INDENT]
End Select

Where PAc_ALA is an integer. Anyway, I tried in a different way:

Code:
Select Case True[INDENT]Case PAc_ALA <> 5, PAc_ALA <> 10, PAc_ALA <> 15, PAc_ALA <> 20, PAc_ALA <> 25, PAc_ALA <> 30, PAc_ALA <> 35, PAc_ALA <> 40[/INDENT]
[INDENT]      Do whatever[/INDENT]
End Select

But it's not working anyway. What it's happenning?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
The CASE statement is evaluating for ALL conditions..


Compare:
Code:
For Cntr = 1 To 20

     PAc_ALA = Cntr 


    Select Case PAc_ALA
    
        Case Is <> 5, Is <> 10
            Debug.Print PAc_ALA
    
    End Select


Next Cntr

with:

Code:
For Cntr = 1 To 20

     PAc_ALA = Cntr 


    Select Case PAc_ALA
    
        Case Is <> 5
            Debug.Print PAc_ALA
        Case Is <> 10
            Debug.Print PAc_ALA
    
    End Select


Next Cntr




In the first instance it's evaluating ALL criteria at once..

Thus, the number 5... when evaluated... is being asked the question... "Are you equal to BOTH: (NOT 5) AND (NOT 10)


Evaluating the expression 5 = NOT(5) and NOT(10) returns FALSE... because it doesn't meet both criteria.



The second code sample evaluates separately..


So, how do you get what you want?


Code:
For Cntr = 1 To 20


    PAc_ALA = Cntr


    Select Case PAc_ALA
    
        Case 5, 10, 15, 20, 25, 30
            
            'Do Nothing
            
        Case Else
        
            'Put your code here
        
            Debug.Print PAc_ALA
    
    End Select


Next Cntr


Hope that helps explain!
 
Upvote 0
Hi MartyS


Thanks a lot for your answer. So VBA translates the comma as AND operator, not as OR operator.


What I'm needing is an execution in case of the integer PAc_ALA would be different from 5, 10, etc...


What you propouse is a good solution, complex, but at the end of the day good solution.

Thanks a lot!!!
 
Upvote 0
This is another way of doing it
Code:
   Select Case True
      Case PAc_ALA Mod 5
      'do something
   End Select
 
Upvote 0
Nope. the comma is treated as OR.
There's a good article on the Select Case here https://www.exceltrick.com/formulas_macros/vba-select-case-statement/


5klxmt.jpg



The AND in the explanation was referring to there being more than one evaluation, and not the actual BOOLEAN evaluation itself:


CASE Statement: I'm doing evaluation on this criteria AND this critera AND this criteria.. AND this criteria also...


Yes, the BOOLEAN logic is OR:

2u4l9wx.jpg



and it's also true that the last column is doing evaluations on <>5 and <>10


The only criteria that could be excluded in a case statement:

Code:
[COLOR=#333333][FONT=monospace]Case Is <> 5, Is <> 10[/FONT][/COLOR]


would be an impossible number that was simultaneously 5 and 10




Apologies if it wasn't clear enough...
 
Last edited:
Upvote 0
This is another way of doing it
Code:
   Select Case True
      Case PAc_ALA Mod 5
      'do something
   End Select

This is great unless the data presented to the SELECT statement goes beyond, say 45, yet the criteria doesn't.


E.G. If the criteria is specifically: <>5, <>10, <>15, <>20, <>25, <>30,<>35, <>40 (i.e. doesn't go beyond 40)

and the data is any value in the range of, say, 1 - 100



It also wouldn't work if, say, any of the criteria was a value other than an increment of 5

E.G. <>5, <>9, <>15, <>23, <>25, <>31,<>34, <>40



If it's always infinite increments of 5 (or the data doesn't go beyond 44)... PAc_ALA Mod 5 is so much more elegant!
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top