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