Variable name in command to change in every loop

Question

I have a command that sets a variable's value in a Do until loop and I want this command to refer to a different variable each time (e.g. "datapoint1" in the first loop, "datapoint2" in the second, etc.). I tried to use a "For" loop instead of "Do Until", but that creates a problem as the times that I want the loop to be performed fluctuates a lot (the "Do Until" loop is part of a larger "For" loop).

So my question is: Is there a way to make that single command line to refer to different variables in each loop?

I have a sample of my code below for reference. Thank you all in advance for your help.

 Dim datapoint(0 to 15) As Integer

        Do Until hello = 1
        rw = False
        h = h + 1 'I have tried to use this variable to count the number of each loop.
        name = ActiveCell.Offset(0, -8).Text
        level = ActiveCell.Offset(0, -4).Text
        If InStr(name, fullname) > 0 Then 'Checks if the line contains data for a specific name
            If level = "High" Then: datapoint(h) = ActiveCell.Value ' This creates an error
            Set r = ActiveCell
            Do Until rw = True
            Set r = r.Offset(1, 0)
            If r.EntireRow.Hidden = False Then
                r.Select
                rw = True
            End If
            Loop

        Else

            hello = 1
        End If

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Please also tell which errors you get. — Pᴇʜ, May 07 '20 at 08:57
Thank you for the tip Pᴇʜ, I will have a look at the thread you've shared. The error I get is: Run-time error '6': Overflow. Basically, I think that the h that is within the parentheses is not taking the value of the variable h. — SavvasT, May 07 '20 at 12:20
Note that `datapoint` is only defined for `0 To 15` so if your `h` is `>15` it will throw an "Overflow" error. Check the value of `h` in case of error. Also your `datapoint` is of type `Integer` so the maximum value can be `32767`. If you try to write bigger values from `ActiveCell.Value` then you get an overflow too. So also check the value in `ActiveCell.Value` in case of error. — Pᴇʜ, May 07 '20 at 12:24
Solved! The error was occurring because the cell value was greater than 32767. Thank you for your help Pᴇʜ! — SavvasT, May 07 '20 at 12:46

score 0 · Accepted Answer · answered May 07 '20 at 12:51

Note that datapoint is only defined for 0 To 15 so if your h is >15 it will throw an "Overflow" error. Check the value of h in case of error. Also your datapoint is of type Integer so the maximum value can be 32767.

If you try to write bigger values from ActiveCell.Value then you get an overflow too. So also check the value in ActiveCell.Value in case of error.

If you use values greater than that you must use another type:

Dim datapoint(0 to 15) As Long    'for bigger integer values
Dim datapoint(0 to 15) As Double  'for decimal values

Variable name in command to change in every loop

1 Answers1