Run-time error 1004 while using dynamic array

Question

I have 15 names in column A. While trying to ReDim and populate an already declared array, and extracting the elements (names) into column E, the following code gave me hiccups saying that it was run-time error 1004 (application-defined or object-defined error).

Dim f() as string

Private Sub CommandButton1_Click()
    Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
    ReDim Preserve f(Finalrow - 1)

For i = 0 To Finalrow - 1
    f(i) = Sheet1.Cells(i, 1) ''''This line causes the glitch (error 1004)
Next

For i = 0 To Finalrow - 1
    Sheet1.Cells(i, 5) = f(i)
Next

End Sub

Gary's Student · Accepted Answer · 2018-04-22 11:46:17Z

2

The loop variable i starts with 0; this kills the Cells() function.

(there may be other errors)

answered Apr 22, 2018 at 11:46

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ashleedawg Over a year ago

*...since there is no column number zero.

shrivallabha.redij · Accepted Answer · 2018-04-22 12:28:46Z

1

Is this academic exercise?

If not then one can easily use:

Private Sub CommandButton1_Click()
Dim FinalRow As Long
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Sheet1.Range("E1:E" & FinalRow).Value = Sheet1.Range("A1:A" & FinalRow).Value
End Sub

answered Apr 22, 2018 at 12:28

shrivallabha.redij

5,9021 gold badge15 silver badges28 bronze badges

Collectives™ on Stack Overflow

Run-time error 1004 while using dynamic array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related