[VBA] Populate Array with Range Values applying an IF criteria

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!!!

hi, Eldexoly, the reason for error is that your array has no dimensions. Check the following code as is and then try to comment redim line:

Sub test()
Dim x()
ReDim x(1)
x(1) = 1
End Sub

For your particular task I would suggest to take the whole range into array:

dim MyArray as Variant
...
MyArray = Sheets("Values").range("K6:K17")

and while looping check if particular array member has a value, example:

if x(i,1)<>"" then
...'your actions
end if

Hi watersev:

Thank you for your answer! I'll check it right now.

Regarding your solution, I've a question. In the following piece of code you posted:

if x(i,1)<>"" then
...'your actions
end if

You defined the element of the Array as x(i,1). If my Array has only one dimension, why did you put 2 elements on it?

By the way, I've always defined Arrays in the following way, without specifiend the number of elements:

Dim myArray() As Variant
Dim i as Integer

myArray = Array("Sheet1", "Sheet2", "Sheet3")

For i = LBound(myArray) To UBound(myArray)
' actions...
Next

Regards!

try this code and take a note of the array dimensions:

Sub test()
Dim x As Variant
x = Range("k6:k17")
MsgBox "Array rows number: " & UBound(x) & vbNewLine & "Array columns count: " & UBound(x, 2)
End Sub