Conditionally positioning arrays

I have a row of data in cells a1:d1 Each cell has a value of either 0,1,2 or 3
lets assume the row contains 1,3,0,2 (these are calculated elsewhere and are
not unique - it is a "random" lists of 0's, 1's etc

I have created rows of data that have generated data in them. Lets say the
row is 1,2,5,9,8,4 (again this data is generated elsewhere) - this is an
intermediate row in say row 99 so a99=1, b99=2, c99=5, d99=9 and so on

I want to create a number of rows starting in row 5 that are created
according to the data in row 1, such that in my example the 99 row is created
1 times at row 5, then offset to the right by1 cell and created 3 times in
rows 6,7,8, then offset by 1 and created 0 times, then offset by 1 and
created 2 times in rows 9,10.

So the sheet starting in row 5 would look like (~ is a blank cell)

1,2,5,9,8,4~~~
~1,2,5,9,8,4~~
~1,2,5,9,8,4~~
~1,2,5,9,8,4~~
~~~1,2,5,9,8,4
~~~1,2,5,9,8,4

I have complete freedom over what goes on, so for example if i needed to
create a column that was simply row 1 rotated i could do that.

Thanks in advance for your help.

Dave

I have set aside the maximun number of rows in the table and have currently set them to zero.

I think I am looking for a function to go in the cells but really i am just looking for a way of getting the data in the cells so i can add em up.

The situation is that i am creating small investments based on cash on hand and am trying to work out the income. So the cash on hand allows up to 3 loans per month for a maximun numberof 36 months. So there could be 108 loans but there could also be 0 loans.

The numbers that i want to put in the rows are the loan returns per month (i know the value of that) and so I want a way of being able to create variable number of rows depending on that 0-3 number and to put the data in those rows - i think i can do that last part by use of index and column functions.

I hope that helps

Hi Dave

Assuming the data in A1:D1 and in A99 to the right as you wrote in your post, try:

Try:

Please Login or Register  to view this content.

Works great - thanks - now all i have to do it get it to run when the data is changed. Are there things like 'execute subroutine on change of value' events in VBA?

Thank you!!

I know have all the parts to my answer - the trick is to put them together and have them work !!!