VBA - filling an array with values from many separate cells-CodePudding

I need a simple solution for inputing numbers from the cells specified in one cell into an array.

E.g. A1 is filled with particular text: A3, B4, D10, F1:F45

I need values from these cells in the array in VBA - how can I do this withput specifing everything in code? I've tried something like:

Dim x() As Array
x = Range(Range("A1").Value).Value

However it doesn't work apparently :)

CodePudding user response：

You want the Array() to capture multiple values, e.g.:

Sub generateArray()
    Dim testArray As Variant
    testArray = Array(Cells(1, 1).Value, Cells(2, 2).Value)
    Debug.Print testArray(0)
    Debug.Print testArray(1)
End Sub

Regarding the use of adding multiple arrays to your array, you will want to create a composite array dimensioned to your two previous ubound(), or simply redim parameters and keep adding to the new array. See

CodePudding user response：

Since A3, B4, D10, F1:F45 are non-contiguous, and presumably you want a 1D array, you can use a loop:

Dim rng As Range
Set rng = Range(Range("A1").Value)
    
Dim arr As Variant
ReDim arr(1 To rng.Count)
    
Dim counter As Long
counter = 1
    
Dim cell As Range
For Each cell In rng
    arr(counter) = cell.Value
    counter = counter   1
Next