Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
219 views
Welcome To Ask or Share your Answers For Others

1 Answer

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share

548k questions

547k answers

4 comments

86.3k users

...