Looping through columns and arrays

Hi all, new to vb scripting and trying to get work with an excel document and loop through an column on data and add it to an array

VB Code:

Dim i
i = 0
Dim bv
Dim arrBan()
Dim CellValue
bv = 3
'check the length of the column to create an array of good length
Do Until objXL.Cells(bv,15).value = ""
arrBan(i) = objXL.Cells(bv,15).value
i = i + 1
bv = bv + 1
loop

from this i get a subscript out of range error, the column can be any length so i cant set a value to the array but a dynamic array should work fine right?
also to see if my thought proccess is right, overall what i am trying to do is compare 2 excel docs to a third, based on the numbers in this column, what i was going to do was get the get the numbers from the 2 sheets and compare them to the third. long proccess i know but i am slowly working on it :) any advice would be greatly apprecaited

EDIT: fixed the looping issue the array had to be set to a value, now a better question is there a way to dynamically set a array length based on a number of columns

You just need to use the Redim statement to size the array each time you add an element. When you Dim a dynamic array, VB doesn't actually initialize it until you give it bounds:

VB Code:

Do Until objXL.Cells(bv, 15).Value = ""
    ReDim Preserve arrBan(i)
    arrBan(i) = objXL.Cells(bv, 15).Value
    i = i + 1
    bv = bv + 1
Loop

ok, now i make it a little more interesting,
is there a way to use the do loop to read through multiple worksheets? the values are all in the same column i just need to collect them into an array or something so that i can work with later

Something like this should do the trick:

VB Code:

Dim oBook As Workbook, oSheet As Worksheet, i As lonb, bv As Long
Dim arrBan() As Variant, CellValue As Variant
 
Set oBook = Application.ActiveWorkbook
 
For Each oSheet In oBook.Worksheets
    bv = 3
    i = 0
    Do Until objXL.Cells(bv, 15).Value = ""
        ReDim Preserve arrBan(i)
        arrBan(i) = objXL.Cells(bv, 15).Value
        i = i + 1
        bv = bv + 1
    Loop
Next oSheet
 
Set oBook = Nothing

EDIT: You'll have to either process the resulting arrays inside of the For loop, or if you want everything in one big array, take out the i = 0 line.