returning arrays to excel

jimjam

New Member
Joined
Dec 5, 2005
Messages
43
Hi everyone,

I have created an array in VBA which I want to put back into excel at the end of my programme. The Array will be a 20 by 6 array, and filled with numbers.

However, in columns A and B (say starting in row 1), I want to put in columns 1 and 4 from my array.

I;m thinking there must be an easy way of doing this, rather than creating 3 new 20X2 arrays and then dumping each one in excel using smthg like:

[a1].Resize(endrow, 2).Value = Arr1

this beginner would appreciate any help!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi JimJam

You can pull a specific value out of your array to plonk in a specific cell:

Code:
Range("A1").Value = myArray(10,5)

for example. You can wrap this up in For...Next loops so that it automates the process. Obviously slower than simply dumping the whole array into Excel as it's structured, but hopefully less problematic than creating new arrays.
 

jimjam

New Member
Joined
Dec 5, 2005
Messages
43
thanks for that

I have actually redone my array so that now I want to but columns 2-4 of my array in columns a b and c. can i do something with redim preserve to dump this data in without a loop?

Thanks in advance
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
You can't change the number of dimensions of an array with Redim Preserve, so that isn't really an option (you'd have to paste it in a range, then pick up the bits of the range you wanted, before pasting it back to its final destination - which sounds like a pain to me). I honestly think you'd be best to use a loop - something along the lines of the following should suffice:

Code:
With Activesheet
   for i = 2 to 4
         For j = Lbound(myArray,1) to Ubound(myArray,1)
               .Cells(j,i) = myArray(j,i)
Next:Next
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,112,802
Messages
5,542,585
Members
410,561
Latest member
Sasha Lawrence
Top