Pasting a big range of values from an arraylist to a worksheet

Hi,

I have been cracking my head trying to figure this out. I'm trying to create a function that will fill 8 columns and an unspecified number of rows in a spreadsheet with data from an arraylist. Basically each column has a different heading and will contain different data and the number of rows will be different depending on the parameters passed. I will be determining the number of rows from the length of another arraylist

To start, I read that we could initialize our arraylist without setting its size. I did this.
Dim ArrayValues() As String

Then, after finding the number of rows I will have from my data, I redimensionalized it as such
ReDim ArrayValues(2 To 51, 1 To 8)

Is that even right? :'(

This is just dummy data but this is essentially how I'm trying to fill the arraylist. I'm not even sure if this is working.

Please Login or Register  to view this content.

And for actually outputting these data onto the worksheet, I have met no luck. I've been using google like crazy to help me but to no avail. Mostly I find help for populating one column with data. What about 8 columns and a variable number of rows.

HELP! I'm really stuck. Thank you in advance for your help!

Hi,

This will paste your array into the worksheet:

Please Login or Register  to view this content.

Thanks for the reply. But unfortunately, it's still not working. The same thing happens, it only spits "account names" in Cell A1. Not even A2.

Am I not initializing/referencing things properly? This is pretty much what I do at the start before looping through r.

Please Login or Register  to view this content.

Then here I loop through r from 2 to 51.

so, this works fine for me:

Please Login or Register  to view this content.

Please test this code in a blank workbook. There is maybe a mistake in filling the array, as you say you are resizing it with variable values. Remove "On error resume next" to find the codeline of problem then.

Yes this is possible, what would you like to enter as parameters?, How should the array size calculated?, Where does the values come from?
Right now you have only strings in the parameter list, how to make an array with that information?

you cannot have a worksheet function populate cells other than the cell(s) that call the function. if you return an array the user has to array-enter the function into sufficient cells to contain the result array

As an alternative you can enter the parameters into a userform which is opened by a shortcut, e.g.: Book1.xlsm
Open the UserForm with Ctrl+D or the button on sheet1.

Thanks for the input JosephP.

Very interesting... So there isn't a way to populate the whole worksheet with just one function? so for example if my cells("A1:A15") need account names and cells ("B1:B15") need company IDs, etc, they would have to be done in separate functions?

And tehneXus, thanks for your help. I assigned a button to my sub and it works well now. The only thing is that the users are looking to have it as a function in the long run.

You can achieve it with one function but that function must be array entered into all the output cells-or the function returns an array and you pass indices to it with each call, like the INDEX function