Excel VBA - Assign Values of 2D Array to Range of Cells

Question

I'm trying to assign a 2D array of size 183x6 to a new sheet, populating the blank cells from A1:G182 with the values of the array. For reference, my array is called "Directory" and the empty sheet I want to populate is called "List".

I've tried two different approaches, one by explicitly assigning the specified range to the array as such:

Worksheets("List").Range(Cells(1,1), Cells(UBound(Directory, 1) + 1, UBound(Directory, 2) + 1)) = Directory

And also by trying to iterate through each entry in the array:

For i = 0 To UBound(Directory, 1)
    For j = 0 To UBound(Directory, 2)
        Worksheets("List").Range(Cells(i + 1, j + 1), Cells(i + 1, j + 1)) = Directory(i,j)
    Next j
Next i

In both cases, I get the error:

Run-time error '1004':
Application-defined or object defined error.

Any ideas what could be happening? I appreciate your help.

Possible duplicate of Using a variable to define a range.

user4039065
– user4039065

2016-01-11 23:57:02 +00:00
Commented Jan 11, 2016 at 23:57 — user4039065
– user4039065, Commented Jan 11, 2016 at 23:57

Fadi · Accepted Answer · 2016-01-11 23:47:52Z

11

Try:

Worksheets("List").Range("A1").Resize(UBound(Directory, 1) + 1, UBound(Directory, 2) + 1).Value = Directory

Or:

For i = 0 To UBound(Directory, 1)
    For j = 0 To UBound(Directory, 2)
        Worksheets("List").Range(Worksheets("List").Cells(i + 1, j + 1), Worksheets("List").Cells(i + 1, j + 1)) = Directory(i,j)
    Next j
Next i

answered Jan 11, 2016 at 23:47

Fadi

3,3223 gold badges21 silver badges43 bronze badges

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

1 Comment

Kevin M Over a year ago

That first suggestion nailed it, thank you very much!

Gary's Student · Accepted Answer · 2016-01-12 00:05:35Z

5

You don't need any loops to move an array to memory. For example:

Sub Array2Range()
   Dim Directory(1 To 182, 1 To 6) As Variant
   Dim rng As Range, i As Long, j As Long

   For i = 1 To 6
      For j = 1 To 182
         Directory(j, i) = i * j
      Next j
   Next i

   Set rng = Sheets("List").Range("A1:F182")

   rng = Directory

End Sub

will produce:

answered Jan 12, 2016 at 0:05

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

Excel VBA - Assign Values of 2D Array to Range of Cells

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related