Store values in variables based on count

[Microsmith]

I'm trying to write a macro that declares variables in a dim statement where
the number of variables are based on a count (number of cells in a range for
example). To illustrate I present the following macro :

Sub SetVariableNames()

Dim myVarRange As Range
Dim myCell(1 To 6) As Variant
Dim x As Integer
Dim i As Integer

'Assigns values to myCell based on contents of myRange

i = 1
x = ActiveSheet.Range("myRange").Count
Set myVarRange = Worksheets(1).Range("myRange")

For i = 1 To x
myCell(i) = myVarRange.Cells(i).Value
Next i

End Sub

The above macro work fine becasue I specified in the dim statement that I
had 6 values to assign to myCell(1 to 6). However, I would like the macro
to set the upper range based on the count of cells in the selected range so
that values could be stored to a variable based on each value in the range
which could change. I can't figure out how to do that. The variables would
be used elsewhere in the routine woth an action based on the variable's
value. I originally tried myCell(1 to X) but I get a variable not defined
error.

Thanks in advance for the help!

Microsmith

 

[JE McGimpsey]

You could use ReDim:

Dim myCell As Variant
Dim x As Long

x = ActiveSheet.Range("myRange").Count
ReDim myCell(1 to x)

but you might also want to consider simply using myCell as a
two-dimensional array):

Dim myCell As Variant
Dim i As Long

myCell = Worksheets(1).Range("myRange").Value
For i = 1 To UBound(myCell, 1)
Debug.Print myCell(i, 1)
Next i

 

[JLatham]

Use a dynamic array.

Dim myCell() as Variant

then after you've set your x variable based on the count, add this line
Redim myCell(1 to x)

 

[Microsmith]

Thanks JLatham and JE McGimpsey. This is exactly what I needed!