Select Case Syntax

  • Thread starter Thread starter Minitman
  • Start date Start date
M

Minitman

Greetings,

I am trying to use a select case statement where the case is every 7th
number. In a For/Next loop it would be written:

For i = 14 to 42 step 7
...
Next i

How would you do that for Select Case?

Anyone help will be appreciated.

-Minitman
 
Dim d As Double

d = num / 7
If Int(num / 7) <> d Then d = -1
Select Case d
Case 2:
Case 3
etc, to Case 6
Case -1: ' not a multiple of 7
Case Else: ' some other multiple of 7
End Select

Regards,
Peter T
 
Sub JustInCase()
Dim i As Integer
i = Application.InputBox(Prompt:="Number??", Type:=1)
Select Case i
Case 14
MsgBox ("fourteen")
Case 21
MsgBox ("twenty one")
Case 28
MsgBox ("twenty eight")
Case 35
MsgBox ("thirty five")
Case 42
MsgBox ("fourty two")
Case Else
MsgBox ("something else")
End Select
End Sub
 
Hey Peter,

Thanks for the reply. I'm not sure how your solution works, but I
suspect I didn't give enough information so here is a bit more info.

I hope this will clear up any misunderstanding without throwing out
too much detail. I have tried to remove any actual code and leave
only the focus route, from TextBox to the Select Case I am trying to
effect.

**************************************************************************
Private Sub I_42_Change()
CellChange "I_", 42
End Sub

Private Sub I_42_Enter()
CellEnter "I_", 42, True
End Sub

Private Sub I_42_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CellExit "I_", 42
End Sub

....(And all of the other 144 TextBox events)


Private Sub I_90_Change()
CellChange "I_", 90
End Sub

Private Sub I_90_Enter()
CellEnter "I_", 90
End Sub

Private Sub I_90_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CellExit "I_", 90
End Sub
****************************************************************************************

These are the first three and the last three TextBox code which cover
the three main events for each TextBox (Change, Enter and Exit)

Next they are sent to the event code:

*****************************************************************************************
Public Sub CellChange(vName As Variant, vNum As Variant)
CalcBoxes vName, vNum
CellFormat vName, vNum
End Sub

Public Sub CellEnter(vName As Variant, vNum As Variant)
CellColor vName, vNum, "In"
End Sub

Public Sub CellExit(vName As Variant, vNum As Variant)
CalcBoxes vName, vNum
CellFormat vName, vNum
CellColor vName, vNum, "Out"
End Sub
***********************************************************************************

The first of the sorting subs is:

***********************************************************************************
Public Sub CalcBoxes(vName As Variant, vNum As Variant)
Select Case vName
Case "I_"
Select Case vNum
<These are the case listing I'm trying to streamline>
Case 42, 49, 56, 63, 70, 77, 84, 91
InvCol_1 vName, vNum
Case 43, 50, 57, 64, 71, 78, 85, 92
InvCol_2 vName, vNum
Case 45, 52, 59, 66, 73, 80, 87, 94
InvCol_4 vName, vNum
Case 46, 53, 60, 67, 74, 81, 88, 95
InvCol_5 vName, vNum
Case 47, 54, 61, 68, 75, 82, 89, 96
InvCol_6 vName, vNum
Case 48, 55, 62, 69, 76, 83, 90, 97
InvCol_7 vName, vNum
End Select
End Select
End Sub
*************************************************************************************

This does work as is, But I would like to stream line the code,if
possible.

Any ideas, thought or suggestions?

Any help is appreciated, Thanks.

-Minitman
 
Hey Gary's Student,,

Thanks for the reply.

As you can see from the reply to Peter reply, I already have a simple
solution that is working. What I am trying to do is streamline my
code. Sorry for the misunderstanding.

-Minitman
 
This does work as is, But I would like to stream line the code,if
possible.

Any ideas, thought or suggestions?
Click to expand...

Hi. Here is just a very general idea.

We first note:

'=MOD(42,7)+1
'=MOD(50,7)+1
'=MOD(97,7)+1
'
'1
'2
'7

Sub Demo()
Dim vNum
Dim x
vNum = 43 '<- Example

x = (vNum Mod 7) + 1

If x <> 3 Then
Run "InvCol_" & fx, 3, 4
End If
End Sub


Sub InvCol_2(x, y)
Debug.Print x * y
End Sub

'= = = = = =
Very general of course
HTH
Dana DeLouis
 
Assuming you accidentally left out the case covering the vNum values of 44, 51, 58, 65, 72, 79, 86, 93; then here is the simplified Case "I_" you asked about...

Case "I_"
Select Case (vNum - 42) Mod 7
Case 0
InvCol_1 vName, vNum
Case 1
InvCol_2 vName, vNum
Case 2
InvCol_4 vName, vNum
Case 3
InvCol_4 vName, vNum
Case 4
InvCol_5 vName, vNum
Case 5
InvCol_6 vName, vNum
Case 6
InvCol_7 vName, vNum
End Select
 
Run "InvCol_" & fx, 3, 4
Click to expand...

OOps. Don't know how it was changed to fx.
Anyway...

x = (vNum Mod 7) + 1

If x <> 3 Then
Run "InvCol_" & x, vName, vNum
End If


= = = = = = =
HTH :>)
Dana DeLouis
 
Of course, if you use Application.Run to run your "InvCol_x" subroutines, you can eliminate the Select Case block entirely...

Case "I_"
Application.Run "InvCol_" & (1 + (vNum - 42) Mod 7)

Again, the above covers the case where your vNum values could be 44, 51, 58, 65, 72, 79, 86, 93 which you omitted from your original Select Case block.

--
Rick (MVP - Excel)


Assuming you accidentally left out the case covering the vNum values of 44, 51, 58, 65, 72, 79, 86, 93; then here is the simplified Case "I_" you asked about...

Case "I_"
Select Case (vNum - 42) Mod 7
Case 0
InvCol_1 vName, vNum
Case 1
InvCol_2 vName, vNum
Case 2
InvCol_4 vName, vNum
Case 3
InvCol_4 vName, vNum
Case 4
InvCol_5 vName, vNum
Case 5
InvCol_6 vName, vNum
Case 6
InvCol_7 vName, vNum
End Select
 
I just read Dana's post and see that she came up with the same idea as I did (using the Run method of the Application object), but her expression...

(vNum Mod 7) + 1

is simpler looking than mine...

(1 + (vNum - 42) Mod 7)

I would just point out that Dana's method works because your starting number (42) is a multiple of 7... the method I posted would work for any starting number (provided, of course, that the sequence of numbers still skipped by 7 each time).

--
Rick (MVP - Excel)


Of course, if you use Application.Run to run your "InvCol_x" subroutines, you can eliminate the Select Case block entirely...

Case "I_"
Application.Run "InvCol_" & (1 + (vNum - 42) Mod 7)

Again, the above covers the case where your vNum values could be 44, 51, 58, 65, 72, 79, 86, 93 which you omitted from your original Select Case block.

--
Rick (MVP - Excel)


Assuming you accidentally left out the case covering the vNum values of 44, 51, 58, 65, 72, 79, 86, 93; then here is the simplified Case "I_" you asked about...

Case "I_"
Select Case (vNum - 42) Mod 7
Case 0
InvCol_1 vName, vNum
Case 1
InvCol_2 vName, vNum
Case 2
InvCol_4 vName, vNum
Case 3
InvCol_4 vName, vNum
Case 4
InvCol_5 vName, vNum
Case 5
InvCol_6 vName, vNum
Case 6
InvCol_7 vName, vNum
End Select
 
Hey Dana,

Thanks for the reply.

It appears both you and Rick came up with similar ideas, only Rick
took it a bit further.

Again, thanks.

-Minitman
 
Hey Rick,

Thanks. I plugged this interpretation of your solution into my code
and it errored out. Here's the code :

Dim iCol As Integer

Case "I_"
Select Case vNum
Case 42 to 97
iCol = (1+(vNum-42)Mod 7)
If Not iCol = 3 then _
Application.Run "InvCol_" & iCol(vName, vNum)

This look like it should work but doesn't. Since there is no code for
column 3, it is considered an illegal condition. Hence, the if
statement to exclude it.

When I try to run this code I get an "expected array" error.

And what do I have to do to get "Application.Run" to work?

Any idea's?

-Minitman
 
You originally posted this...

I meant the following to replace all of it...

You are showing an extra Select Case (for the vNum) which, if I understand
your set up, is not required (as a matter of fact, it would interfere with
the operation of the code I posted). If you need it, you can do the test for
iCol not being 3, but don't put it inside the extra Select Case block (the
one with Case 42 to 97). As I said, the code line I posted replaces all the
Select Case statements you showed originally... the only one you need is the
Case "I_" that you showed. Now, if there are other cases beside "I_", and I
assume there are, I can't say if the code line I posted can cover them or
not (mainly because you didn't show them to us), but the concept of using
the Application.Run can probably be used (you would just have to modify the
string representing the subroutine's name and any arguments the subroutine
required).
 
Thanks. I plugged this interpretation of your solution into my code
and it errored out. Here's the code :
Click to expand...

Hi. Your original code was not returning a value...
It was just running the macro...

Case 42, 49, 56, 63, 70, 77, 84, 91
InvCol_1 vName, vNum


Note the differences here...

Sub Demo()
Dim Ans

'Return a value
Ans = Run("M", 2, 3)

'Or just run macro
Run "M", 2, 3
End Sub

Function M(x, y)
M = x * y
End Function

= = = = = = =
HTH
Dana DeLouis
 
Hey Rick

I just read your last post and modified yours and Danas code slightly
and now debug can't find the macro. Here is the code:

Dim iCol As Integer

Case 42 To 97
iCol = (vNum Mod 7) + 1
If Not iCol = 3 Then Run "InvCol_" & iCol, vName, vNum
End Select

The strange thing is that debug says that it can't fin "InvCol_1" when
I run the code for the first column. I'm other words, debug is
listing a correct sub as the sub it can't find???

Any ideas as to what's going on?

-Minitman
 
Hey Dana,

You are correct, The purpose of the procedure is indeed to sort the
name of the TextBox that started this chain of events to the macro
that is going to return a value into different TextBoxes in these
columns.

Again thanks for your help.

-Minitman
 
Ok, here is the entire sub:

Public Sub CalcBoxes(vName As Variant, vNum As Variant)
Dim iCol As Integer
Select Case vName
Case "I_"
Select Case vNum
Case 4, 6, 8, 10, 12, 14
CalculateServiceFee vName, vNum
Case 42 To 97
iCol = (vNum Mod 7) + 1
If Not iCol = 3 Then Run "InvCol_" & iCol, vName, vNum
End Select
End Select
End Sub

This is the code that can't find the macro to run and then names a
good macro that it can't find (as I mentioned in my last post)..

-Minitman,
 
Hey Rick,

Sorry about not getting back sooner.

What you show for the original post does contain a few error. There
is only one InvCon_4 and there is no 3rd column code. So the
corrected code should be:

Case "I_"
Select Case (vNum - 42) Mod 7 +1
Case 10
InvCol_1 vName, vNum
Case 2
InvCol_2 vName, vNum
Case 4
InvCol_4 vName, vNum
Case 5
InvCol_5 vName, vNum
Case 6
InvCol_6 vName, vNum
Case 7
InvCol_7 vName, vNum
End Select

I messed up in my post, please accept my apology. I realize that
having the +1in the select case test is not significant, but it
matches up the case numbers with the procedure a bit better.

I am having a problem using Application.Run as in:

Case "I_"
Application.Run "InvCol_" & (1 + (vNum - 42) Mod 7)

I can't get it to work. VB cannot find the macro 'InvCol_1'. Which
is the correct procedure name for the first column. I even tried
running it with only Run "InvCol_" & (1 + (vNum - 42) Mod 7),
Removing the word Application made no difference. Homey suggested
moving the InvCol codes to a general module. So I tried that and
discovered that the Me.Controls is not available there.

It appears that if I cannot use a variable when calling the InvCol
procedures, Then I was forced back to the corrected original select
case solution.

Do you know of a way I can use your final solution? I really like the
way it looks.

If it makes any difference, I am running Excel 2003 on an XP box

Thanks for the assistance you have already given, I really do
appreciate it.

-Minitman
 
Back
Top