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!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top