Conditionally positioning arrays

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:

Sub CopyShift()
Dim rCopy As Range
Dim lRow As Long, lCol As Long

Set rCopy = Range("A99", Range("A99").End(xlToRight))

lRow = 5
For lCol = 1 To 4
    If Cells(1, lCol) <> 0 Then
        rCopy.Copy Cells(lRow, lCol).Resize(Cells(1, lCol), rCopy.Columns.Count)
        lRow = lRow + Cells(1, lCol)
    End If
Next lCol
End Sub

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?