Checking Array Elements

[Rocky0201]

Hi...

I have a Variant Array, Dim vArr as Variant

I split into the array a field from my spreadsheet that may or may not contain a value that I am finding. My example:

ReDim vArr(5)

vArr = Split(Sheets("Master Schedule").Cells(i, 11), ";")

I then move each element into another array;

v(9) = vArr(0) '// Task 1
v(10) = vArr(1) '// Task 2
v(11) = vArr(2) '// Task 3
v(12) = vArr(3) '// Task 4
v(13) = vArr(4) '// Task 5

If I had a value of "This is a Test" in Master Schedule, vArr(0) will contain "This is a Test". I get a runtime error (subscript out of range) executing the next statement, v(10) = vArr(1).

If I had a value of "This is a Test; This is also a Test" in the Master Schedule, vArr(0) will contain "This is a Test" and vArr(1) will contain "This is also a Test". I get a runtime error (subscript out of range) executing the next statement, v(11) = vArr(2).

Is there a way to check how many elements were created in vArr during the Split? If so, perhaps I could use a For loop given the number of elements created from the Split. It's possible that the Master Schedule field would be blank therefore, I would not try to move any data from vArr.

Thank you for the help...

 

[mikerickson]

Dim i As Long

vArr = Split(Sheets("Master Schedule").Cells(i, 11), ";")

For i = 0 to Ubound(vArr)
    v(9+ i) = vArr(i)
Next i

 

[gregtx81]

for the array varr:
lbound(varr) = the lowest index
ubound(varr) = the highest index

Under default settings the lowest index=0. So the number of elements will be ubound(varr)+1.

Also, the split resizes the array...so no need for the redim statement beforehand.

Try msgbox varr(0). If that isn't causing an error then it's your v(10) whose index is out of bounds.

 

[Rocky0201]

Many thanks to you both, mikerickson & gregtx81.