Select case question

[Brettjg]

I'd like to compress the following code down to just the one Select block.
The only difference between the two is that in the second I'm testing for two
conditions. There are actually five of these altogether, so the code gets
quite lengthy as a result of the "DO SOME STUFF" part (which doesn't change).
The range that I'm selecting doesn't change either - only the lp_cnt and the
number of tests changes m1L, m2L etc).

If lp_cnt = 1 Then
Select Case Lsh.Cells(lk_rw, lk_cl).Value
Case m1L
"DO SOME STUFF"
End Select
End If

If lp_cnt = 2 Then
Select Case Lsh.Cells(lk_rw, lk_cl)
Case m1L, m2L
"DO SOME STUFF"
End Select
End If

Is there a way to set the case test for a variable amount of tests i.e. (and
I know this wouldn't work because I can't have a statement between Select and
Case)
If lp_cnt = 1 Then: Case m1L
If lp_cnt = 2 Then: Case m1L, m2L
If lp_cnt = 3 Then: Case m1L, m2L, m3L
If lp_cnt = 4 Then: Case m1L, m2L, m3L, m4L
If lp_cnt = 5 Then: Case m1L, m2L, m3L, m4L, m5L

Regards, Brett

 

[Rick Rothstein]

One other question I forgot to ask you back in the other thread you started
this idea in... is the "DO SOME STUFF" in each Case section the same code or
is it different? If different, in what way?

 

[Jacob Skaria]

If "Do some Stuff" is same; the only change is lp_cnt and Lsh.Cells(lk_rw,
lk_cl).Value. I am just trying to understand why m1L is mentioned in all
cases.

If this post helps click Yes

 

[Brettjg]

Hi Rick, no change in "DO SOME STUFF"

One other question I forgot to ask you back in the other thread you started
this idea in... is the "DO SOME STUFF" in each Case section the same code or
is it different? If different, in what way?

 

[Brettjg]

That's probably a good Q Jacob. Immediatelt before the select is:

If Range("S1").Offset(0, 1) > 0 Then: m1L = Range("S1").Offset(0,
1).Value: lp_cnt = lp_cnt + 1
If Range("S2").Offset(0, 1) > 0 Then: m2L = Range("S2").Offset(0,
1).Value: lp_cnt = lp_cnt + 1
If Range("S3").Offset(0, 1) > 0 Then: m3L = Range("S3").Offset(0,
1).Value: lp_cnt = lp_cnt + 1
If Range("S4").Offset(0, 1) > 0 Then: m4L = Range("S4").Offset(0,
1).Value: lp_cnt = lp_cnt + 1
If Range("S5").Offset(0, 1) > 0 Then: m5L = Range("S5").Offset(0,
1).Value: lp_cnt = lp_cnt + 1

S1 S2 etc aren't the real names (just to shorten the code in this post). I'm
setting the value of lp_cnt which in turn will set whether I look for (m1S)
or (m1S and m2S) etc. There may be a better way to express this
but...............
"DO SOME STUFF" is identical each time through the 5 selects.
Regards, Brett

 

[Jacob Skaria]

Write DOSOMESTUFF as a separate procedure. I assume you need to pass the
value of m1L...m5L. Once you get the values for m1L,....M5L..place the below
code


If lp_cnt > 0 And m1L = Lsh.Cells(lk_rw, lk_cl).Value Then Call
DO_SOME_STUFF(m1L)
If lp_cnt > 1 And m2L = Lsh.Cells(lk_rw, lk_cl).Value Then Call
DO_SOME_STUFF(m2L)
If lp_cnt > 2 And m3L = Lsh.Cells(lk_rw, lk_cl).Value Then Call
DO_SOME_STUFF(m3L)
If lp_cnt > 3 And m4L = Lsh.Cells(lk_rw, lk_cl).Value Then Call
DO_SOME_STUFF(m4L)
If lp_cnt > 4 And m5L = Lsh.Cells(lk_rw, lk_cl).Value Then Call
DO_SOME_STUFF(m5L)

Sub DO_SOME_STUFF(mLGen)
'Place your code here

End Sub




If this post helps click Yes

 

[Brettjg]

Hi Jacob, yes I had thought of that, but I was wondering if there was a more
elegant way of Case testing for multiple values. In other words if I don't
know whether I want to do "Case m1S" or "Case m1S, m2S"or "Case m1S, m2S, m3S"
etc, is there a way of setting the case generically?

 

[Jacob Skaria]

Before answering could you let me know the below queries. DSS = Do Some Stuff

1. Are you using m1L...m5L values within DSS. OR Is there any variable which
is changing within DSS.
2. Do we need to execute DSS 5 times if lp_cnt = 5

If this post helps click Yes

 

[Brettjg]

No, not using the m1S etc. The variables don't change within DSS. DSS has to
be run 5 times - it's just coincidence that 5 comes up twice (5 different
loan applications can have up to 5 loan splits each)

This is the actual code (excuse my shorthand names) and there are 5 of these
within a loop that runs about 100 times, and that is within another loop that
runs up to 5 times (surprisingly quickly):
If lp_cnt = 1 Then
Select Case Lsh.Cells(lk_rw, lk_cl).Value
Case m1L
If Lsh.Cells(lk_rw, lk_cl).Value > 0 Then
Cells(rwP, clP).Value = Lsh.Cells(lk_rw,
lk_nm).Value
If Lsh.Cells(lk_rw, lk_lg) > 0 Then
Cells(rwP, clP + 1).Value =
Lsh.Cells(lk_rw, lk_lg).Value & " " & Lsh.Cells(lk_rw, lk_ln).Value & " " &
Lsh.Cells(lk_rw, lk_mn).Value
ElseIf Lsh.Cells(lk_rw, lk_mn) > 0 Then
Cells(rwP, clP + 1).Value =
Lsh.Cells(lk_rw, lk_ln).Value & " " & Lsh.Cells(lk_rw, lk_mn).Value
ElseIf Lsh.Cells(lk_rw, lk_ln) > 0 Then
Cells(rwP, clP + 1).Value =
Lsh.Cells(lk_rw, lk_ln).Value
End If
rwP = rwP + 1
End If
End Select
End If

 

[Rick Rothstein]

From what you have posted so far, I think (not tested) that you can use this
code...

Dim V As Variant
For Each V In Array("S1", "S2", "S3", "S4", "S5")
If Range(V).Offset(0, 1).Value > 0 Then
If Lsh.Cells(lk_rw, lk_cl).Value = Range(V).Offset(0, 1).Value Then
'"DO SOME STUFF"
Exit For
End If
Next

in order to execute your "DO SOME STUFF" code. I don't think there is any
need to execute your Select Case block nor the five blocks of If..Then
tests. I think the above replaces them UNLESS you make use of m1L, m2L, etc.
and/or your in your lp_cnt counter within your "DO SOME STUFF" code directly
(although if you do, I would be willing to bet that part of your code could
reference back to the Range(V).Offset(0, 1).Value values directly, still
then eliminating the need for those variables... hard to say without seeing
all of your code and having an explanation of what it should be doing).

 

[Jacob Skaria]

To compress your code the best way to put this is inside a For loop after you
get lp_cnt count.

For lngTemp = 1 to lp_cnt
'DSS
Next


If this post helps click Yes

 

[Rick Rothstein]

Given that you want to run the "DO SOME STUFF" more than once (up to 5
times), I shouldn't have put the Exit For statement in my code (also I left
off an End If statement). Also given you don't use m1L, etc. in your "DO
SOME STUFF" (you said that in another message you posted), then I am pretty
sure you should be able to replace your five If..Then tests and your Select
Case block with this code...

Dim V As Variant
For Each V In Array("S1", "S2", "S3", "S4", "S5")
If Range(V).Offset(0, 1).Value > 0 Then
If Lsh.Cells(lk_rw, lk_cl).Value = Range(V).Offset(0, 1).Value Then
'"DO SOME STUFF"
End If
End If
Next

 

[Jacob Skaria]

I mean with the IF condition in between

For lngTemp = 1 to lp_cnt
If <condition> Then
'DSS
End If
Next

 

[Brettjg]

Thankyou Jacob, a combination of your answer and Rick's is absolutely
perfect. Regards, Brett

 

[Brettjg]

Thank you so much Rick, that is magical - took a little adjusting, but it
cuts the code WAY down. Regards, Brett

 

[Rick Rothstein]

You are quite welcome... I'm glad I was able to help out some.

 

[Brettjg]

It's funny you should mention the Exit For - I thought the same as you, but I
ran it as you posted (after putting the missing End If in) and damn me if it
didn't work perfectly, so I left the Exit For there. I'll run it without it
and see what happens. The final code now looks like:

Do While lk_rw <= Lsh.Range("row.end.oth.cc").Row
Dim V As Variant
For Each V In Array("S" & cnt_A & ".1", "S" & cnt_A & ".2", "S"
& cnt_A & ".3", "S" & cnt_A & ".4", "S" & cnt_A & ".5")
If Range(V).Offset(0, 1).Value > 0 And Lsh.Cells(lk_rw,
lk_cl).Value = Range(V).Offset(0, 1).Value Then
Cells(rwP, clP).Value = Lsh.Cells(lk_rw, lk_nm).Value
If Lsh.Cells(lk_rw, lk_ln) > 0 Then: Cells(rwP, clP
+ 1).Value = Lsh.Cells(lk_rw, lk_ln).Value
If Lsh.Cells(lk_rw, lk_mn) > 0 Then: Cells(rwP, clP
+ 1).Value = Lsh.Cells(lk_rw, lk_ln).Value & " " & Lsh.Cells(lk_rw,
lk_mn).Value
If Lsh.Cells(lk_rw, lk_lg) > 0 Then: Cells(rwP, clP
+ 1).Value = Lsh.Cells(lk_rw, lk_lg).Value & " " & Lsh.Cells(lk_rw,
lk_ln).Value & " " & Lsh.Cells(lk_rw, lk_mn).Value
rwP = rwP + 1
Exit For
End If
Next
lk_rw = lk_rw + 1
Loop

Thanks very much for your help and persistance.