Simplify my code please!

[GiraffetheGeek]

Hi all,

I have the following:

If  r = 0 Or r = 11 Or r = 12 Then
     r = r + 1
Else
     r = r + 2
End If

This works fine, but I want to extend it to IF r = any multiple of 11 up to 300.

Is there any way of doing this without manually typing r = 11 Or r = 22 etc.

Is there any limit to how many OR's VBA can check?

 

[CWatts]

You can use the Mod command, which works the same as the =MOD worksheet function, but with different syntax than the worksheet function:

If r Mod 11 = 0 And r <= 300 Then 
     'Your code

 

[Trebor76]

Hi there,

Yes, there's a limit (same as its worksheet function counterpart) of 30 for AND and OR statements.

That said, try this...

If r = 0 Or r >= 11 And r <= 300 Then
        r = r + 1
    Else
        r = r + 2
    End If

...or this:

Select Case r
        Case 0, 11 To 300
            r = r + 1
        Case Else
            r = r + 2
    End Select

HTH

Robert

 

[GiraffetheGeek]

Robert,

If I read your one correctly then if r = 0 or and any number 11-300 the r would be increased by 1, else it would be increased by 2.

What I want is if r = 0, 11, 22, 33 or any multiple of 11 less then 300 then I want r increased by 1 else any other number r needs to be increased by 2

Sorry after just typing that last paragraph I noticed I made an error typing in my code in my first post. The 12 should be 22.

CWatts - I am just about to try yours.

 

[GiraffetheGeek]

CWatts, thanks that worked a treat.

 

[Trebor76]

Sorry my bad - I didn't realise you needed multiples.

As well as CWatts's clever solution, here's a slightly different way:

If (r / 11 = CInt(r / 11)) And r <= 300 Then
        r = r + 1
    Else
        r = r + 2
    End If

HTH

Robert

 

[Blade Hunter]

one line solution:

r = r + IIf((r / 11 = CInt(r / 11)) And r <= 300, 1, 2)