Hi all,

I use to work with Arrays, but I populate them in a manual way. This time I need to populate the array with the data (numbers as text) present in a range.

What's my goal? I've a range (Sheets("Values").range("K6:K17")) with some numbers stored as text. These numbers are the names of certain tabs. The array loaded with those names will be the input of a subsequent loop, to work on those selected tabs.

I want to apply an IF condition: if any of the cells in range K6:K17 is empty, discard it and do not load it into the array. In this way, the range K6:K17 has 14 cells and maybe only 4 have data. If I do not load those empty values in myArray, the array will have a dimension of 4 and when I will loop over the myArray I would be able to use the boundaries LBound(myArray) and UBound(myArray).

I've coded the following chain:

Sub Populate_Array()

Dim myArray() As String
Dim myValue As range
Dim i As Long

i = 1
For Each myValue In Sheets("Values").range("K6:K17")
    If IsEmpty(myValue) = True Then
    'do nothing
    Else
    'fill in the Array
    myArray(i) = myValue.Value
    i = i + 1
    End If
Next

End Sub

I obtain the following error:

Run-time error "9":

Subscript out of range.

I try to debug the error, and it highlights:

    myArray(i) = myValue.Value

Thank you in advance!!!