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

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?

--
Rick (MVP - Excel)


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

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

Hi Rick, no change in "DO SOME STUFF"

"Rick Rothstein" wrote:

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?

--
Rick (MVP - Excel)


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

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

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

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

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

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

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

--
Rick (MVP - Excel)


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

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

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

--
Rick (MVP - Excel)


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

--
Rick (MVP - Excel)


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

I mean with the IF condition in between

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

--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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

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

"Jacob Skaria" wrote:

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

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

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

--
Rick (MVP - Excel)


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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

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.

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

--
Rick (MVP - Excel)


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