Assigning values to variant crashes, why?

2019-12-16 19:23发布

I have this cute code here, which goes through a tons of files with selecting folder and has to collect stuff to a new sheet. In this part I would like to store the header data to an array, because it is static and has to be appended to each row. The header information comes from the cellPosition array, which contains cell values like B2. Inside the for cycle I get error on save_fix_gen(i)= crashes some how at the 2nd element (i=2) and I can't figure it out why. Any help would be appreciated with a big plus!

fyi

(
    Dim save_fix_gen() As Variant, cellPositions() As Variant, i As Integer
    cellPositions() = get_general 'this comes from a function and
)
    Do While myFile <> ""

    Set ActualWorkBook = Workbooks.Open(myPath & myFile)

    ReDim save_fix_gen(0)
    ActualWorkBook.Worksheets("SheetName").Activate
    For i = LBound(cellPositions) To UBound(cellPositions)
        save_fix_gen(i) = CStr(Evaluate(cellPositions(i)))
        i = i + 1
        ReDim Preserve save_fix_gen(i)
    Next

    Loop

1条回答
冷血范
2楼-- · 2019-12-16 19:53

This worked, thank you. Simply set the array to the max size of the input.

ReDim save_fix_gen(UBound(cellPositions))
ActualWBK.Worksheets("Bid Leveling Template").Activate
For i = LBound(cellPositions) To UBound(cellPositions)
    save_fix_gen(i) = CStr(Evaluate(cellPositions(i)))
    i = i + 1
    'ReDim Preserve save_fix_gen(i)
Next
查看更多
登录 后发表回答