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


"Minitman" wrote in message
...
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

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

--
Gary''s Student - gsnu200909


"Minitman" wrote:

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
.

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



On Sun, 17 Jan 2010 04:40:01 -0800, Gary''s Student
wrote:

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

On 1/17/2010 11:01 AM, Dana DeLouis wrote:
Run "InvCol_" & fx, 3, 4

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

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

On Sun, 17 Jan 2010 11:08:44 -0500, Dana DeLouis
wrote:

On 1/17/2010 11:01 AM, Dana DeLouis wrote:
Run "InvCol_" & fx, 3, 4

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

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



On Sun, 17 Jan 2010 11:12:16 -0500, "Rick Rothstein"
wrote:

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)


"Rick Rothstein" wrote in message ...
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

You originally posted this...

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 meant the following to replace all of it...

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

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).

--
Rick (MVP - Excel)


"Minitman" wrote in message
...
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



On Sun, 17 Jan 2010 11:12:16 -0500, "Rick Rothstein"
wrote:

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)


"Rick Rothstein" wrote in message
...
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

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

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



On 1/17/2010 6:59 PM, Minitman wrote:
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



On Sun, 17 Jan 2010 11:12:16 -0500, "Rick Rothstein"
wrote:

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)


"Rick wrote in message ...
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



--
= = = = = = =
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


On Sun, 17 Jan 2010 17:59:57 -0600, Minitman
wrote:

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



On Sun, 17 Jan 2010 11:12:16 -0500, "Rick Rothstein"
wrote:

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)


"Rick Rothstein" wrote in message ...
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,

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

On Sun, 17 Jan 2010 19:37:39 -0500, Dana DeLouis
wrote:

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

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



On 1/17/2010 6:59 PM, Minitman wrote:
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



On Sun, 17 Jan 2010 11:12:16 -0500, "Rick Rothstein"
wrote:

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)


"Rick wrote in message ...
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

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,

On Sun, 17 Jan 2010 19:19:08 -0600, Minitman
wrote:

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


On Sun, 17 Jan 2010 17:59:57 -0600, Minitman
wrote:

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



On Sun, 17 Jan 2010 11:12:16 -0500, "Rick Rothstein"
wrote:

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)


"Rick Rothstein" wrote in message ...
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 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
On Sun, 17 Jan 2010 19:34:19 -0500, "Rick Rothstein"
wrote:

You originally posted this...

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 meant the following to replace all of it...

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

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).