InList()?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In VFP there is a Function called InList() where you may build an If-EndIf
expression around a list of candidates.

At the moment, in a Macro, I have this:

If rngCell.Value = "PEN-Pensionable Earnings" Or _
rngCell.Value = "PN2-Pension Plan Charlotte Hly HN2" Or _
rngCell.Value = "TSP-Textron Savings Plan" Then

However, on examining my data I discover that there are perhaps another
DOZEN possible candidates. Do I have to continue building that horrendous Or
expression, or can I pack all these pups into a Variable and apply my If End
If expression to just the variable?
 
No Inlist or equivalent command. One way:

Dim v as Variant, res as Variant
v = Array("PEN-Pensionalble Earnings", _
"PN2-Pension Plan Charlotte Hly HN2", _
"TSP-Textron Savings Plan")
res = Application.Match(rng,v,0)
if not iserror(res) then
' matches item in the list

end if

You can add to the list. If res isn't an error value, then you can use
v(res)
to tell you which it matched.
 
I KNEW there was a way, and I can see that is precisely what I was looking
for! Thanks a million!!!!
 
You can use Select Case type statement like the following:

Select Case rngCell.Value
Case "PEN-Pensionable Earnings","Pen2-Pension Plan Charlotte Hyl
HN2","TSP-Textron Savings Plan"
<Statements>
Case <another set of values, if you like including greater than or less
than>
<Statements>
Case Else 'Can be used to run statements for those items not met in the
above conditions
<Statements>
End Select

Note, you only need to have 1 Case statement such as the first one above
stated, but you can have other Case statements within the same Select
structure as long as you are only testing against that one value, such as in
this case, just against one cell value. This will not only help it be a bit
cleaner for such situations, but it also helps in being more efficient to
some extent too.

Ronald R. Dodge, Jr.
Master MOUS 2000
 
Very nice -- Thank you!!
--
Dave
Temping with Staffmark
in Rock Hill, SC


Ronald Dodge said:
You can use Select Case type statement like the following:

Select Case rngCell.Value
Case "PEN-Pensionable Earnings","Pen2-Pension Plan Charlotte Hyl
HN2","TSP-Textron Savings Plan"
<Statements>
Case <another set of values, if you like including greater than or less
than>
<Statements>
Case Else 'Can be used to run statements for those items not met in the
above conditions
<Statements>
End Select

Note, you only need to have 1 Case statement such as the first one above
stated, but you can have other Case statements within the same Select
structure as long as you are only testing against that one value, such as in
this case, just against one cell value. This will not only help it be a bit
cleaner for such situations, but it also helps in being more efficient to
some extent too.

Ronald R. Dodge, Jr.
Master MOUS 2000
Click to expand...
 
I've hacked merrily along with this, but following values in Watch, it
appears I have failed to understand it correctly. Here's what I have:

v = Array("PEN-Pensionalble Earnings", _
"PN2-Pension Plan Charlotte Hly HN2", _
"TSP-Textron Savings Plan")
res = Application.Match(rngCell.Value, v, 0)

and when rngCell.Value = "PEN-Pensionalble Earnings", it is returning an
error. I also tried it just using the form in your example of

res = Application.Match(rng, v, 0)

and got the same result.

Where did I go wrong, mother darling?
 
Back
Top