Ronald Dodge gave me this in his answer to my warlier question about InList():

Select Case rngCell.Value
Case "PEN-Pensionable Earnings","Pen2-Pension Plan Charlotte Hyl
HN2","TSP-Textron Savings Plan"

Which I did, and it works perfectly for my purposes -- in fact, back in my
VFP environment I was a big fan of Case statements. However my need here is
to build the case expression programmatically, because on various passes
through a large For..Next Loop, the contents of the needed selection will
vary based on what is found on the current WS.

Here's what I've been doing:

For Each rngWorkCell In Range("K" & lngFoundRow, "K" & lngFoundRow +
lngCount)
For intI = LBound(blnCodes) To UBound(blnCodes) 'Step through the Codes
If blnCodes(intI) Then 'If it's still True, then we need to add it to
the search list
If Left(rngWorkCell, 3) = strCodes(intI) Then 'Matches the Code, Add
it to the list
If Len(strSearchList) = 0 Then 'Have to create a "legal" list
strSearchList = strSearchList + strCodes(intI) 'First one
Else
strSearchList = strSearchList + ", " + strCodes(intI) '..all others
need the comma separator
End If
blnCodes(intI) = False 'Turn off the flag so we don't use this guy
again on the Sheet
End If
End If
Next intI 'Get another Code

...prior to firing off

Select Case Left(rngSubCountCell.Value, 3)
Case strSearchList

Now, by running a creful Debug, I have learned that the case expression
looks like:

Case "PN2, OT-, REG"

...which, of course, guarantees the Case will never be found.

How can I build that list into a variable so that it delivers this:

Case "PN2", "OT-", "REG"

???
--
Dave
Temping with Staffmark
in Rock Hill, SC

Dave, I just took a quick look at your code and noticed you are using + for a
concatenate symbol. You would be better off using &. The + symbol
sometimes causes problems because your variables will try to add
mathematically instead of conactenate.

"Dave Birley" wrote:

> Ronald Dodge gave me this in his answer to my warlier question about InList():
>
> Select Case rngCell.Value
> Case "PEN-Pensionable Earnings","Pen2-Pension Plan Charlotte Hyl
> HN2","TSP-Textron Savings Plan"
>
> Which I did, and it works perfectly for my purposes -- in fact, back in my
> VFP environment I was a big fan of Case statements. However my need here is
> to build the case expression programmatically, because on various passes
> through a large For..Next Loop, the contents of the needed selection will
> vary based on what is found on the current WS.
>
> Here's what I've been doing:
>
> For Each rngWorkCell In Range("K" & lngFoundRow, "K" & lngFoundRow +
> lngCount)
> For intI = LBound(blnCodes) To UBound(blnCodes) 'Step through the Codes
> If blnCodes(intI) Then 'If it's still True, then we need to add it to
> the search list
> If Left(rngWorkCell, 3) = strCodes(intI) Then 'Matches the Code, Add
> it to the list
> If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> strSearchList = strSearchList + strCodes(intI) 'First one
> Else
> strSearchList = strSearchList + ", " + strCodes(intI) '..all others
> need the comma separator
> End If
> blnCodes(intI) = False 'Turn off the flag so we don't use this guy
> again on the Sheet
> End If
> End If
> Next intI 'Get another Code
>
> ..prior to firing off
>
> Select Case Left(rngSubCountCell.Value, 3)
> Case strSearchList
>
> Now, by running a creful Debug, I have learned that the case expression
> looks like:
>
> Case "PN2, OT-, REG"
>
> ..which, of course, guarantees the Case will never be found.
>
> How can I build that list into a variable so that it delivers this:
>
> Case "PN2", "OT-", "REG"
>
> ???
> --
> Dave
> Temping with Staffmark
> in Rock Hill, SC

Like the ant that tried to move the rubber tree plant, I had "High Hopes" of
that, but after changing the +s to &s I still got this:

"PN2, HOL, REG".

I thought of referencing an array, but haven't figured out how to configure
it so that the Case expression could use it.
--
Dave
Temping with Staffmark
in Rock Hill, SC


"JLGWhiz" wrote:

> Dave, I just took a quick look at your code and noticed you are using + for a
> concatenate symbol. You would be better off using &. The + symbol
> sometimes causes problems because your variables will try to add
> mathematically instead of conactenate.
>
> "Dave Birley" wrote:
>
> > Ronald Dodge gave me this in his answer to my warlier question about InList():
> >
> > Select Case rngCell.Value
> > Case "PEN-Pensionable Earnings","Pen2-Pension Plan Charlotte Hyl
> > HN2","TSP-Textron Savings Plan"
> >
> > Which I did, and it works perfectly for my purposes -- in fact, back in my
> > VFP environment I was a big fan of Case statements. However my need here is
> > to build the case expression programmatically, because on various passes
> > through a large For..Next Loop, the contents of the needed selection will
> > vary based on what is found on the current WS.
> >
> > Here's what I've been doing:
> >
> > For Each rngWorkCell In Range("K" & lngFoundRow, "K" & lngFoundRow +
> > lngCount)
> > For intI = LBound(blnCodes) To UBound(blnCodes) 'Step through the Codes
> > If blnCodes(intI) Then 'If it's still True, then we need to add it to
> > the search list
> > If Left(rngWorkCell, 3) = strCodes(intI) Then 'Matches the Code, Add
> > it to the list
> > If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> > strSearchList = strSearchList + strCodes(intI) 'First one
> > Else
> > strSearchList = strSearchList + ", " + strCodes(intI) '..all others
> > need the comma separator
> > End If
> > blnCodes(intI) = False 'Turn off the flag so we don't use this guy
> > again on the Sheet
> > End If
> > End If
> > Next intI 'Get another Code
> >
> > ..prior to firing off
> >
> > Select Case Left(rngSubCountCell.Value, 3)
> > Case strSearchList
> >
> > Now, by running a creful Debug, I have learned that the case expression
> > looks like:
> >
> > Case "PN2, OT-, REG"
> >
> > ..which, of course, guarantees the Case will never be found.
> >
> > How can I build that list into a variable so that it delivers this:
> >
> > Case "PN2", "OT-", "REG"
> >
> > ???
> > --
> > Dave
> > Temping with Staffmark
> > in Rock Hill, SC

Tried this:

If Len(strSearchList) = 0 Then 'Have to create a "legal" list
strSearchList = strSearchList & """ & strCodes(intI) & """ 'First one
Else
strSearchList = strSearchList & """ & ", " & """ + strCodes(intI) &
""" '..all others need the comma separator
End If

...and it didn't work either -- told me it was looking for an end of
statement on the Else guy.
--
Dave
Temping with Staffmark
in Rock Hill, SC


"JLGWhiz" wrote:

> Dave, I just took a quick look at your code and noticed you are using + for a
> concatenate symbol. You would be better off using &. The + symbol
> sometimes causes problems because your variables will try to add
> mathematically instead of conactenate.
>
> "Dave Birley" wrote:
>
> > Ronald Dodge gave me this in his answer to my warlier question about InList():
> >
> > Select Case rngCell.Value
> > Case "PEN-Pensionable Earnings","Pen2-Pension Plan Charlotte Hyl
> > HN2","TSP-Textron Savings Plan"
> >
> > Which I did, and it works perfectly for my purposes -- in fact, back in my
> > VFP environment I was a big fan of Case statements. However my need here is
> > to build the case expression programmatically, because on various passes
> > through a large For..Next Loop, the contents of the needed selection will
> > vary based on what is found on the current WS.
> >
> > Here's what I've been doing:
> >
> > For Each rngWorkCell In Range("K" & lngFoundRow, "K" & lngFoundRow +
> > lngCount)
> > For intI = LBound(blnCodes) To UBound(blnCodes) 'Step through the Codes
> > If blnCodes(intI) Then 'If it's still True, then we need to add it to
> > the search list
> > If Left(rngWorkCell, 3) = strCodes(intI) Then 'Matches the Code, Add
> > it to the list
> > If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> > strSearchList = strSearchList + strCodes(intI) 'First one
> > Else
> > strSearchList = strSearchList + ", " + strCodes(intI) '..all others
> > need the comma separator
> > End If
> > blnCodes(intI) = False 'Turn off the flag so we don't use this guy
> > again on the Sheet
> > End If
> > End If
> > Next intI 'Get another Code
> >
> > ..prior to firing off
> >
> > Select Case Left(rngSubCountCell.Value, 3)
> > Case strSearchList
> >
> > Now, by running a creful Debug, I have learned that the case expression
> > looks like:
> >
> > Case "PN2, OT-, REG"
> >
> > ..which, of course, guarantees the Case will never be found.
> >
> > How can I build that list into a variable so that it delivers this:
> >
> > Case "PN2", "OT-", "REG"
> >
> > ???
> > --
> > Dave
> > Temping with Staffmark
> > in Rock Hill, SC

Is this the type of sting you want:

?"""" & "BOL" & ",""" & "DOL" & ",""" & "MOL"""

run in Immediate window that returns this:
"BOL,"DOL,"MOL"

notice the use of multiple quotes to get the strings.

Hopt that helps in some way

Philip

"Dave Birley" wrote:

> Like the ant that tried to move the rubber tree plant, I had "High Hopes" of
> that, but after changing the +s to &s I still got this:
>
> "PN2, HOL, REG".
>
> I thought of referencing an array, but haven't figured out how to configure
> it so that the Case expression could use it.
> --
> Dave
> Temping with Staffmark
> in Rock Hill, SC
>
>
> "JLGWhiz" wrote:
>
> > Dave, I just took a quick look at your code and noticed you are using + for a
> > concatenate symbol. You would be better off using &. The + symbol
> > sometimes causes problems because your variables will try to add
> > mathematically instead of conactenate.
> >
> > "Dave Birley" wrote:
> >
> > > Ronald Dodge gave me this in his answer to my warlier question about InList():
> > >
> > > Select Case rngCell.Value
> > > Case "PEN-Pensionable Earnings","Pen2-Pension Plan Charlotte Hyl
> > > HN2","TSP-Textron Savings Plan"
> > >
> > > Which I did, and it works perfectly for my purposes -- in fact, back in my
> > > VFP environment I was a big fan of Case statements. However my need here is
> > > to build the case expression programmatically, because on various passes
> > > through a large For..Next Loop, the contents of the needed selection will
> > > vary based on what is found on the current WS.
> > >
> > > Here's what I've been doing:
> > >
> > > For Each rngWorkCell In Range("K" & lngFoundRow, "K" & lngFoundRow +
> > > lngCount)
> > > For intI = LBound(blnCodes) To UBound(blnCodes) 'Step through the Codes
> > > If blnCodes(intI) Then 'If it's still True, then we need to add it to
> > > the search list
> > > If Left(rngWorkCell, 3) = strCodes(intI) Then 'Matches the Code, Add
> > > it to the list
> > > If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> > > strSearchList = strSearchList + strCodes(intI) 'First one
> > > Else
> > > strSearchList = strSearchList + ", " + strCodes(intI) '..all others
> > > need the comma separator
> > > End If
> > > blnCodes(intI) = False 'Turn off the flag so we don't use this guy
> > > again on the Sheet
> > > End If
> > > End If
> > > Next intI 'Get another Code
> > >
> > > ..prior to firing off
> > >
> > > Select Case Left(rngSubCountCell.Value, 3)
> > > Case strSearchList
> > >
> > > Now, by running a creful Debug, I have learned that the case expression
> > > looks like:
> > >
> > > Case "PN2, OT-, REG"
> > >
> > > ..which, of course, guarantees the Case will never be found.
> > >
> > > How can I build that list into a variable so that it delivers this:
> > >
> > > Case "PN2", "OT-", "REG"
> > >
> > > ???
> > > --
> > > Dave
> > > Temping with Staffmark
> > > in Rock Hill, SC

It's alive, it's ALIVE!!!

strSearchList = """" & "BOL" & """,""" & "DOL" & """,""" & "MOL"""
?strSearchList
"BOL","DOL","MOL"

Final piece of scary stuff -- make it work in a Case expression (Phew!!!!)
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Philip" wrote:

> Is this the type of sting you want:
>
> ?"""" & "BOL" & ",""" & "DOL" & ",""" & "MOL"""
>
> run in Immediate window that returns this:
> "BOL,"DOL,"MOL"
>
> notice the use of multiple quotes to get the strings.
>
> Hopt that helps in some way
>
> Philip
>
> "Dave Birley" wrote:
>
> > Like the ant that tried to move the rubber tree plant, I had "High Hopes" of
> > that, but after changing the +s to &s I still got this:
> >
> > "PN2, HOL, REG".
> >
> > I thought of referencing an array, but haven't figured out how to configure
> > it so that the Case expression could use it.
> > --
> > Dave
> > Temping with Staffmark
> > in Rock Hill, SC
> >
> >
> > "JLGWhiz" wrote:
> >
> > > Dave, I just took a quick look at your code and noticed you are using + for a
> > > concatenate symbol. You would be better off using &. The + symbol
> > > sometimes causes problems because your variables will try to add
> > > mathematically instead of conactenate.
> > >
> > > "Dave Birley" wrote:
> > >
> > > > Ronald Dodge gave me this in his answer to my warlier question about InList():
> > > >
> > > > Select Case rngCell.Value
> > > > Case "PEN-Pensionable Earnings","Pen2-Pension Plan Charlotte Hyl
> > > > HN2","TSP-Textron Savings Plan"
> > > >
> > > > Which I did, and it works perfectly for my purposes -- in fact, back in my
> > > > VFP environment I was a big fan of Case statements. However my need here is
> > > > to build the case expression programmatically, because on various passes
> > > > through a large For..Next Loop, the contents of the needed selection will
> > > > vary based on what is found on the current WS.
> > > >
> > > > Here's what I've been doing:
> > > >
> > > > For Each rngWorkCell In Range("K" & lngFoundRow, "K" & lngFoundRow +
> > > > lngCount)
> > > > For intI = LBound(blnCodes) To UBound(blnCodes) 'Step through the Codes
> > > > If blnCodes(intI) Then 'If it's still True, then we need to add it to
> > > > the search list
> > > > If Left(rngWorkCell, 3) = strCodes(intI) Then 'Matches the Code, Add
> > > > it to the list
> > > > If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> > > > strSearchList = strSearchList + strCodes(intI) 'First one
> > > > Else
> > > > strSearchList = strSearchList + ", " + strCodes(intI) '..all others
> > > > need the comma separator
> > > > End If
> > > > blnCodes(intI) = False 'Turn off the flag so we don't use this guy
> > > > again on the Sheet
> > > > End If
> > > > End If
> > > > Next intI 'Get another Code
> > > >
> > > > ..prior to firing off
> > > >
> > > > Select Case Left(rngSubCountCell.Value, 3)
> > > > Case strSearchList
> > > >
> > > > Now, by running a creful Debug, I have learned that the case expression
> > > > looks like:
> > > >
> > > > Case "PN2, OT-, REG"
> > > >
> > > > ..which, of course, guarantees the Case will never be found.
> > > >
> > > > How can I build that list into a variable so that it delivers this:
> > > >
> > > > Case "PN2", "OT-", "REG"
> > > >
> > > > ???
> > > > --
> > > > Dave
> > > > Temping with Staffmark
> > > > in Rock Hill, SC

try this:

If Len(strSearchList) = 0 Then 'Have to create a "legal" list
strSearchList = strSearchList & """ & strCodes(intI) & """ 'First one
Else
strSearchList = strSearchList & """" & ", " & """ + strCodes(intI) &
""" '..all others need the comma separator
End If

you were missing a quote.

What I do is place the quote in a variable using chr(34)

HTH

Philip

"Dave Birley" wrote:

> Tried this:
>
> If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> strSearchList = strSearchList & """ & strCodes(intI) & """ 'First one
> Else
> strSearchList = strSearchList & """ & ", " & """ + strCodes(intI) &
> """ '..all others need the comma separator
> End If
>
> ..and it didn't work either -- told me it was looking for an end of
> statement on the Else guy.
> --
> Dave
> Temping with Staffmark
> in Rock Hill, SC
>
>
> "JLGWhiz" wrote:
>
> > Dave, I just took a quick look at your code and noticed you are using + for a
> > concatenate symbol. You would be better off using &. The + symbol
> > sometimes causes problems because your variables will try to add
> > mathematically instead of conactenate.
> >
> > "Dave Birley" wrote:
> >
> > > Ronald Dodge gave me this in his answer to my warlier question about InList():
> > >
> > > Select Case rngCell.Value
> > > Case "PEN-Pensionable Earnings","Pen2-Pension Plan Charlotte Hyl
> > > HN2","TSP-Textron Savings Plan"
> > >
> > > Which I did, and it works perfectly for my purposes -- in fact, back in my
> > > VFP environment I was a big fan of Case statements. However my need here is
> > > to build the case expression programmatically, because on various passes
> > > through a large For..Next Loop, the contents of the needed selection will
> > > vary based on what is found on the current WS.
> > >
> > > Here's what I've been doing:
> > >
> > > For Each rngWorkCell In Range("K" & lngFoundRow, "K" & lngFoundRow +
> > > lngCount)
> > > For intI = LBound(blnCodes) To UBound(blnCodes) 'Step through the Codes
> > > If blnCodes(intI) Then 'If it's still True, then we need to add it to
> > > the search list
> > > If Left(rngWorkCell, 3) = strCodes(intI) Then 'Matches the Code, Add
> > > it to the list
> > > If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> > > strSearchList = strSearchList + strCodes(intI) 'First one
> > > Else
> > > strSearchList = strSearchList + ", " + strCodes(intI) '..all others
> > > need the comma separator
> > > End If
> > > blnCodes(intI) = False 'Turn off the flag so we don't use this guy
> > > again on the Sheet
> > > End If
> > > End If
> > > Next intI 'Get another Code
> > >
> > > ..prior to firing off
> > >
> > > Select Case Left(rngSubCountCell.Value, 3)
> > > Case strSearchList
> > >
> > > Now, by running a creful Debug, I have learned that the case expression
> > > looks like:
> > >
> > > Case "PN2, OT-, REG"
> > >
> > > ..which, of course, guarantees the Case will never be found.
> > >
> > > How can I build that list into a variable so that it delivers this:
> > >
> > > Case "PN2", "OT-", "REG"
> > >
> > > ???
> > > --
> > > Dave
> > > Temping with Staffmark
> > > in Rock Hill, SC

«What I do is place the quote in a variable using chr(34)»

aHAH! I shall give that a shot!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Philip" wrote:

> try this:
>
> If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> strSearchList = strSearchList & """ & strCodes(intI) & """ 'First one
> Else
> strSearchList = strSearchList & """" & ", " & """ + strCodes(intI) &
> """ '..all others need the comma separator
> End If
>
> you were missing a quote.
>
> What I do is place the quote in a variable using chr(34)
>
> HTH
>
> Philip
>
> "Dave Birley" wrote:
>
> > Tried this:
> >
> > If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> > strSearchList = strSearchList & """ & strCodes(intI) & """ 'First one
> > Else
> > strSearchList = strSearchList & """ & ", " & """ + strCodes(intI) &
> > """ '..all others need the comma separator
> > End If
> >
> > ..and it didn't work either -- told me it was looking for an end of
> > statement on the Else guy.
> > --
> > Dave
> > Temping with Staffmark
> > in Rock Hill, SC
> >
> >
> > "JLGWhiz" wrote:
> >
> > > Dave, I just took a quick look at your code and noticed you are using + for a
> > > concatenate symbol. You would be better off using &. The + symbol
> > > sometimes causes problems because your variables will try to add
> > > mathematically instead of conactenate.
> > >
> > > "Dave Birley" wrote:
> > >
> > > > Ronald Dodge gave me this in his answer to my warlier question about InList():
> > > >
> > > > Select Case rngCell.Value
> > > > Case "PEN-Pensionable Earnings","Pen2-Pension Plan Charlotte Hyl
> > > > HN2","TSP-Textron Savings Plan"
> > > >
> > > > Which I did, and it works perfectly for my purposes -- in fact, back in my
> > > > VFP environment I was a big fan of Case statements. However my need here is
> > > > to build the case expression programmatically, because on various passes
> > > > through a large For..Next Loop, the contents of the needed selection will
> > > > vary based on what is found on the current WS.
> > > >
> > > > Here's what I've been doing:
> > > >
> > > > For Each rngWorkCell In Range("K" & lngFoundRow, "K" & lngFoundRow +
> > > > lngCount)
> > > > For intI = LBound(blnCodes) To UBound(blnCodes) 'Step through the Codes
> > > > If blnCodes(intI) Then 'If it's still True, then we need to add it to
> > > > the search list
> > > > If Left(rngWorkCell, 3) = strCodes(intI) Then 'Matches the Code, Add
> > > > it to the list
> > > > If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> > > > strSearchList = strSearchList + strCodes(intI) 'First one
> > > > Else
> > > > strSearchList = strSearchList + ", " + strCodes(intI) '..all others
> > > > need the comma separator
> > > > End If
> > > > blnCodes(intI) = False 'Turn off the flag so we don't use this guy
> > > > again on the Sheet
> > > > End If
> > > > End If
> > > > Next intI 'Get another Code
> > > >
> > > > ..prior to firing off
> > > >
> > > > Select Case Left(rngSubCountCell.Value, 3)
> > > > Case strSearchList
> > > >
> > > > Now, by running a creful Debug, I have learned that the case expression
> > > > looks like:
> > > >
> > > > Case "PN2, OT-, REG"
> > > >
> > > > ..which, of course, guarantees the Case will never be found.
> > > >
> > > > How can I build that list into a variable so that it delivers this:
> > > >
> > > > Case "PN2", "OT-", "REG"
> > > >
> > > > ???
> > > > --
> > > > Dave
> > > > Temping with Staffmark
> > > > in Rock Hill, SC

Well now my strSearchList, which looks like this:

""PN2", "HOL", "REG""

has the quotes and commas in the right place, but the "Case strSearchList"
expression expects there to be no wrapper quotes around it. Yaaargh!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Philip" wrote:

> try this:
>
> If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> strSearchList = strSearchList & """ & strCodes(intI) & """ 'First one
> Else
> strSearchList = strSearchList & """" & ", " & """ + strCodes(intI) &
> """ '..all others need the comma separator
> End If
>
> you were missing a quote.
>
> What I do is place the quote in a variable using chr(34)
>
> HTH
>
> Philip
>
> "Dave Birley" wrote:
>
> > Tried this:
> >
> > If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> > strSearchList = strSearchList & """ & strCodes(intI) & """ 'First one
> > Else
> > strSearchList = strSearchList & """ & ", " & """ + strCodes(intI) &
> > """ '..all others need the comma separator
> > End If
> >
> > ..and it didn't work either -- told me it was looking for an end of
> > statement on the Else guy.
> > --
> > Dave
> > Temping with Staffmark
> > in Rock Hill, SC
> >
> >
> > "JLGWhiz" wrote:
> >
> > > Dave, I just took a quick look at your code and noticed you are using + for a
> > > concatenate symbol. You would be better off using &. The + symbol
> > > sometimes causes problems because your variables will try to add
> > > mathematically instead of conactenate.
> > >
> > > "Dave Birley" wrote:
> > >
> > > > Ronald Dodge gave me this in his answer to my warlier question about InList():
> > > >
> > > > Select Case rngCell.Value
> > > > Case "PEN-Pensionable Earnings","Pen2-Pension Plan Charlotte Hyl
> > > > HN2","TSP-Textron Savings Plan"
> > > >
> > > > Which I did, and it works perfectly for my purposes -- in fact, back in my
> > > > VFP environment I was a big fan of Case statements. However my need here is
> > > > to build the case expression programmatically, because on various passes
> > > > through a large For..Next Loop, the contents of the needed selection will
> > > > vary based on what is found on the current WS.
> > > >
> > > > Here's what I've been doing:
> > > >
> > > > For Each rngWorkCell In Range("K" & lngFoundRow, "K" & lngFoundRow +
> > > > lngCount)
> > > > For intI = LBound(blnCodes) To UBound(blnCodes) 'Step through the Codes
> > > > If blnCodes(intI) Then 'If it's still True, then we need to add it to
> > > > the search list
> > > > If Left(rngWorkCell, 3) = strCodes(intI) Then 'Matches the Code, Add
> > > > it to the list
> > > > If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> > > > strSearchList = strSearchList + strCodes(intI) 'First one
> > > > Else
> > > > strSearchList = strSearchList + ", " + strCodes(intI) '..all others
> > > > need the comma separator
> > > > End If
> > > > blnCodes(intI) = False 'Turn off the flag so we don't use this guy
> > > > again on the Sheet
> > > > End If
> > > > End If
> > > > Next intI 'Get another Code
> > > >
> > > > ..prior to firing off
> > > >
> > > > Select Case Left(rngSubCountCell.Value, 3)
> > > > Case strSearchList
> > > >
> > > > Now, by running a creful Debug, I have learned that the case expression
> > > > looks like:
> > > >
> > > > Case "PN2, OT-, REG"
> > > >
> > > > ..which, of course, guarantees the Case will never be found.
> > > >
> > > > How can I build that list into a variable so that it delivers this:
> > > >
> > > > Case "PN2", "OT-", "REG"
> > > >
> > > > ???
> > > > --
> > > > Dave
> > > > Temping with Staffmark
> > > > in Rock Hill, SC

In foxpro 2.6, as I recall, you could build a variable name in code and then
use it (using the & operator?). Not so in VBA.

Anyway, assuming all the codes are 3 digits so no code is a substring of
another, you can use an approach like:

Sub Tester1()
v = Array("PN2", "HOL", "REG")
v1 = Array("PN1", "PN2", "PN3", "HAL", "HOL", "RIG", "REG")

s = "#"
For i = LBound(v) To UBound(v)
s = s & v(i) & "#"
Next
For i = 1 To 20
r = Int(Rnd() * (UBound(v1) - LBound(v1)))
Select Case True
Case InStr(1, s, v1(r), vbTextCompare) > 0
Debug.Print v1(r), s, "Found"
Case Else
Debug.Print v1(r), "Not Found"
End Select
Next
End Sub

In the immediate window, the results appear as:


HAL Not Found
HOL #PN2#HOL#REG# Found
REG #PN2#HOL#REG# Found
RIG Not Found
PN1 Not Found
HAL Not Found
REG #PN2#HOL#REG# Found
HAL Not Found
HOL #PN2#HOL#REG# Found
HAL Not Found
HAL Not Found
HAL Not Found
PN3 Not Found
PN3 Not Found
PN2 #PN2#HOL#REG# Found
PN1 Not Found
PN2 #PN2#HOL#REG# Found
REG #PN2#HOL#REG# Found
PN1 Not Found
PN3 Not Found

--
Regards,
Tom Ogilvy



"Dave Birley" wrote:

> Well now my strSearchList, which looks like this:
>
> ""PN2", "HOL", "REG""
>
> has the quotes and commas in the right place, but the "Case strSearchList"
> expression expects there to be no wrapper quotes around it. Yaaargh!
> --
> Dave
> Temping with Staffmark
> in Rock Hill, SC
>
>
> "Philip" wrote:
>
> > try this:
> >
> > If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> > strSearchList = strSearchList & """ & strCodes(intI) & """ 'First one
> > Else
> > strSearchList = strSearchList & """" & ", " & """ + strCodes(intI) &
> > """ '..all others need the comma separator
> > End If
> >
> > you were missing a quote.
> >
> > What I do is place the quote in a variable using chr(34)
> >
> > HTH
> >
> > Philip
> >
> > "Dave Birley" wrote:
> >
> > > Tried this:
> > >
> > > If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> > > strSearchList = strSearchList & """ & strCodes(intI) & """ 'First one
> > > Else
> > > strSearchList = strSearchList & """ & ", " & """ + strCodes(intI) &
> > > """ '..all others need the comma separator
> > > End If
> > >
> > > ..and it didn't work either -- told me it was looking for an end of
> > > statement on the Else guy.
> > > --
> > > Dave
> > > Temping with Staffmark
> > > in Rock Hill, SC
> > >
> > >
> > > "JLGWhiz" wrote:
> > >
> > > > Dave, I just took a quick look at your code and noticed you are using + for a
> > > > concatenate symbol. You would be better off using &. The + symbol
> > > > sometimes causes problems because your variables will try to add
> > > > mathematically instead of conactenate.
> > > >
> > > > "Dave Birley" wrote:
> > > >
> > > > > Ronald Dodge gave me this in his answer to my warlier question about InList():
> > > > >
> > > > > Select Case rngCell.Value
> > > > > Case "PEN-Pensionable Earnings","Pen2-Pension Plan Charlotte Hyl
> > > > > HN2","TSP-Textron Savings Plan"
> > > > >
> > > > > Which I did, and it works perfectly for my purposes -- in fact, back in my
> > > > > VFP environment I was a big fan of Case statements. However my need here is
> > > > > to build the case expression programmatically, because on various passes
> > > > > through a large For..Next Loop, the contents of the needed selection will
> > > > > vary based on what is found on the current WS.
> > > > >
> > > > > Here's what I've been doing:
> > > > >
> > > > > For Each rngWorkCell In Range("K" & lngFoundRow, "K" & lngFoundRow +
> > > > > lngCount)
> > > > > For intI = LBound(blnCodes) To UBound(blnCodes) 'Step through the Codes
> > > > > If blnCodes(intI) Then 'If it's still True, then we need to add it to
> > > > > the search list
> > > > > If Left(rngWorkCell, 3) = strCodes(intI) Then 'Matches the Code, Add
> > > > > it to the list
> > > > > If Len(strSearchList) = 0 Then 'Have to create a "legal" list
> > > > > strSearchList = strSearchList + strCodes(intI) 'First one
> > > > > Else
> > > > > strSearchList = strSearchList + ", " + strCodes(intI) '..all others
> > > > > need the comma separator
> > > > > End If
> > > > > blnCodes(intI) = False 'Turn off the flag so we don't use this guy
> > > > > again on the Sheet
> > > > > End If
> > > > > End If
> > > > > Next intI 'Get another Code
> > > > >
> > > > > ..prior to firing off
> > > > >
> > > > > Select Case Left(rngSubCountCell.Value, 3)
> > > > > Case strSearchList
> > > > >
> > > > > Now, by running a creful Debug, I have learned that the case expression
> > > > > looks like:
> > > > >
> > > > > Case "PN2, OT-, REG"
> > > > >
> > > > > ..which, of course, guarantees the Case will never be found.
> > > > >
> > > > > How can I build that list into a variable so that it delivers this:
> > > > >
> > > > > Case "PN2", "OT-", "REG"
> > > > >
> > > > > ???
> > > > > --
> > > > > Dave
> > > > > Temping with Staffmark
> > > > > in Rock Hill, SC