String variable won't work in Case statement

[Bobmax]

Hi
(Apologies if I have posted this in the wrong area -I'm new to Tek-Tips and VBA)

I am trying to reference a function variable within a case statement in the function as follows (part code only to give you an idea):

Function CalcTrueScore(intRating1 as integer, intrating2 as integer,.....intrating20 as integer) as single

Dim intCriterion as integer, sngTrueScore as single

sngTrueScore = 0
intCriterion = 1
Do while inCriterion <= 20
Select Case "intRating" & Cstr(intCriterion)
Case 1
sngTrueScore = sngTrueScore + 100
Case 2
...

When I run this code it does not match any of the case statements (it is treating the concatenated variable name as a string and hence does not match the numbers).

I have tried to use EVAL("intRating" & Cstr(intCriterion)) but this results in a runtime error 2482.

The case statement works OK if I enter the variable name directly (ie type in Case Select intRating1) but I want to cycle through the same coding 20 times rather than create 20 copies of the same code!

Can anyone help me?

Thanks
Bob

 

[SteveR77]

Bob,

Try converting the integer values to strings and then testing the case statement.

To convert the integer to a string. Cstr(integer)

HTH,

Steve

 

[PHV]

And what about something like this ?
Select Case Choose(intCriterion,intRating1,...,intRating20)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Bobmax]

Thanks guys.

You're both champions!

I should have asked a couple of days ago (it would have saved me quite some time!).

Steve - tried yours but it still wouldn't work. Thanks anyhow.

PH - Works a treat!

Your assistance is much appreciated.

Bob