Looping through columns and arrays

[fizban2]

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

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

[Comintern]

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:

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

[fizban2]

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

[Comintern]

Something like this should do the trick:

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.