CODEWITHSUNDEEP
vba
user1012091
user1012091

Reputation: 51

Copying an array to cells

I currently have an array full of data that I would like to transfer to cell G2 all the way until the entire array is exhausted. My current spreadsheet has data in G1 but no data under that.

I have the following code but it doesn't exactly work because I get the error:

Application-defined or object-defined error.

Help fixing this problem would be great. I would appreciate if you could tell me what is wrong with my code and how to fix it rather than providing me with an alternative formulation.

For i = 1 to nFlights
     With Worksheets("Q2").Range("G1")
           .End(xlDown).Offset(1, 0) = Origin(i)
    End With
Next

Upvotes: 3

Views: 11111

Answers (2)

Tim Williams
Tim Williams

Reputation: 166126

I know you didn't want to be shown code, but here's a different approach from your loop:

Dim arr
arr = Array("one","two","three","four")

ActiveSheet.Range("G2").Resize((UBound(arr) - LBound(arr)) + 1, 1).Value = _
                                  Application.Transpose(arr)

Upvotes: 8

Michael Kingsmill
Michael Kingsmill

Reputation: 1863

The offset property just references the cell (in your case), one below the cell that is the base of the call ("G1"). In order to copy the whole array orgin into the column G on the sheet try this:

Dim i As Integer
i = 2

For Each val As String In Origin
    With Worksheets("Q2")
       .Cells(i, 7).Value = Origin(i)
       i = i + 1
    End With
Next

Upvotes: 0

Related Questions