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
---------------
Jacob Skaria


"Brettjg" wrote:

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Brettjg" wrote:

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" wrote:

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
---------------
Jacob Skaria


"Brettjg" wrote:

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" wrote:

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
---------------
Jacob Skaria


"Brettjg" wrote:

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