Solved: Write to excel from VBA array

[Zurigo]

Hi all,

Just realized my previous post is confusing and the procedure i was envisioning way too complicated - apologies.

Assuming i have a 2D VBA array with circa 1mio values, how can i write to a1 in excel all the VBA values in successive iterations? Like i was keeping F9 pressed?

Thank you again.

Cheers, n

[mikerickson]

I'm not sure what you want.

You have an array.
The user presses a button(?)
and the first value appears in A1.
Then what happens? Does the user use the rest of the sheet as normal or???

The user presses the button again and the second value apears in A1, etc.

Also since you have a 2D array, I assume that myArray(1,1) is the first value.
Is the second value (according to your counting) myArray(2,1) or myArray(1,2)?

[Zurigo]

I'm not sure what you want.

You have an array.
The user presses a button(?)
and the first value appears in A1.
Then what happens? Does the user use the rest of the sheet as normal or???

The user presses the button again and the second value apears in A1, etc.

Also since you have a 2D array, I assume that myArray(1,1) is the first value.
Is the second value (according to your counting) myArray(2,1) or myArray(1,2)?

[Zurigo]

Hi & thanks for your post.
What i'm trying to do is as follows:
- populate a VBA array from lots of data in xls (myarray)
- write the content of this array in a1 element by element
- update c1 every time a1 changes
- populate a VBA array with all the c1 the code will generate (bigarray)
- write the content of this last array into excel again

I think i have point 1-3 down, but not sure (i) is set correctly - i.e. Get me all the values c1 wil assume when a1 changes - & keep getting runtime error13 for bigarray(i).

Any help much appreciated.

Cheers, n

[VBA]Sub zurigo
Dim myarray As Variant
Dim bigArray As Variant
myarray = Worksheets("Sheet2").Range("A1:o50")
For x = 1 To 5
For j = 1 To 5
For i = 1 To myarray(x, j)
Range("a1") = myarray(x, j)
'bigArray(i) = Range("c1")
Next
Next
Nex
Range("data").Resize(1, 250) = bigArray
End Sub[/VBA]

[mikerickson]

I'd use a loop like

[VBA]Dim i As Long, j As Long
Dim myArray as Long, myBigArray as Variant

Rem fill myArray

myBigArray = myArray

For i = LBound(myArray,1) To UBound(myArray,1)
For j = LBound(myArray, 2) To UBound(myArray, 2)
Range("B1").Value = myArray(i, j)
myBigArray(i, j) = Range("C1").Value
Next j
Next i[/VBA]

[Zurigo]

You did it.
Much obliged.
Have a good day,
Zurigo

P.s i'd like to tag this post as solved but struggling to find button, etc

[Aussiebear]

Just above your first post is a dropdown "Thread Tools". Select this then scroll down to Mark Thread Solved, select this then click on Perform Action

[Zurigo]

Done. Thx

[rakietu]

good job