Dynamic variable name not working

[Desauv86]

Hi

I know there's several other threads about this, however I can't seem to find a solution on what i'm doing wrong.

Please help me find out what is wrong with below code:

Please Login or Register  to view this content.

I get the error message "The subcript is out of range"

The Whole code is written in a userform, and is executed when a commandbutton is clicked.

Thanks for helping out

[cytop]

Please Login or Register  to view this content.

You declare varLookup with an upper bound of 5, then try to refer to element number 11 ...

[Desauv86]

Then this should work? But it doesn't :-/

Please Login or Register  to view this content.

[:) Sixthsense :)]

Error occurs on which line of code?

[Desauv86]

In this one:

Please Login or Register  to view this content.

[cytop]

No, it won't work. Your array has 5 elements (Regardless of the index number of the lowest) - you've just moved the problem from 1 side of the = sign to the other as now index number 1 does not exist.

I have no idea what the code is supposed to do so can't offer any suggestions.

[:) Sixthsense :)]

Then this should work? But it doesn't :-/

Please Login or Register  to view this content.

How you can expect it to work?

varLookup(i) is depends on For i = 1 To 5 (i.e.) 1 to 5 but your declaration is Dim varLookup(11 To 15) As Integer. So there is no 1 to 5 declaration in varLookup.

This part varLookup(i + 10) will work since it has been covered by this declaration Dim varLookup(11 To 15) As Integer.

So your above quoted code contains a contradictory logic

[Akuini]

Then this should work? But it doesn't :-/

Please Login or Register  to view this content.

That won't work too, because when i=1 there is no varLookup(1).
Maybe this is what you want?

Please Login or Register  to view this content.

[Desauv86]

I have 5 public variables named varLookup11, 12, 13, 14, 15...

I want them to be included in the first code I posted, just like e.g the "Me.Controls("txtPris" & i)

Please Login or Register  to view this content.

So basically i need the variable to be dynamic just like the "Controls".

[cytop]

While you can refer to a control by 'assembling' it's name from some variables or constants, you cannot refer to a variable like that in VBA.

If you really did declare varLookup as an array, you can refer to that using a counter

Please Login or Register  to view this content.

But, only having a small snippet of code out of context, it is difficult to suggest a solution especially considering the names assigned to your existing variables - why varLookup11, 12, 13 ...?

[Desauv86]

Below is the entire code for that Sub.

Please Login or Register  to view this content.

I could do below for every variable varLoolup11, varLoolup12, varLoolup13 etc..., I just wanted to make the code more simple. Because I might be adding more variable

Please Login or Register  to view this content.

I don't know howelse to describe the problem.

But thanks for trying to help

[Akuini]

If I understand you correctly:
What you need from the public variables are the values in it not the variable itself. So why not use an array to store that values and then use that array in the loop.
Something like this:

Please Login or Register  to view this content.

Notes: because arr1 index begin with 0 (the default) so it is 'arr1(i - 1)'

[Desauv86]

That's Works really nice

Thanks so much for your reply.

[Akuini]

That's Works really nice

Thanks so much for your reply.

You're welcome & thanks for the feedback