CODEWITHSUNDEEP
arraysexcelvbarange
user2880722
user2880722

Reputation: 37

How do I fill a 2 dimensional Array with Values and then put the results into a range

Here is the question: "Suppose your program fills a large two-dimensional array called results with values, and you would like it to dump these values into an Excel range. For example, if results is m by n, you would like the program to dump the values into a range with m rows and n columns. One way is to use two nested loops to dump the data one element at a time into the appropriate cell."

What I've got so far:

    Dim MyArray(m, n) As Long
    Dim X as Long
    Dim Y as Long
        For X = 1 To m
        For Y = 1 To n 
            MyArray(X, Y) = Cells(X, Y).Value
        Next Y
        Next X

I really need some help figuring this out I'm totally lost

Upvotes: 1

Views: 11183

Answers (2)

Doug Glancy
Doug Glancy

Reputation: 27478

This fills the array with m rows and n columns and then transfers it all into a range starting in cell A1 of the ActiveSheet:

Sub FillRangeFromArray()
Dim m As Long
Dim n As Long
Dim x As Long
Dim y As Long
Dim MyArray() As String

'set the row and column dimensions
m = 100
n = 5
'redimension the array now that you have m and n
ReDim MyArray(1 To m, 1 To n)
'whenever possible lbound and ubound (first to last element) 
'to loop through arrays, where
'MyArray,1 is the first (row) element and MyArray,2
'is the second (column) element
For x = LBound(MyArray, 1) To UBound(MyArray, 1)
    For y = LBound(MyArray, 2) To UBound(MyArray, 2)
        MyArray(x, y) = x & "-" & y
    Next y
Next x
'fill the range in one fell swoop
'Resize creates a range resized to
'm rows and n columns
ActiveSheet.Range("A1").Resize(m, n).Value = MyArray
End Sub

Upvotes: 2

nutsch
nutsch

Reputation: 5962

Assuming you fill your data somewhere else before that, you can just use

Cells(X, Y).Value=MyArray(X, Y)

Upvotes: 0

Related Questions