Build Select Case Programmatically

[Guest]

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"

???

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

aHAH! I shall give that a shot!

 

[Guest]

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!

 

[Guest]

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

 

[Guest]

Man, I've really made you work for yoru MVP spurs around here <g>!

«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. »

Yup, that was it -- the ampersand.

You are one ingenious dude! By George, I think we may have "got it"! -- I'll
return and report.

 

[Guest]

Funny thing -- I asked several days ago if there was an equivalent to the VFP
"InList() function, and nobody caught that InStr was precisely what I was
looking for. I am now building the strSearchList string the way I was
originally, without any additional Chr(34)s in it, and it ran like a champ.

So Big Tom scores again -- Thanks!!!!!