Help with Loop

Hello,
I am new to VBA and am trying to enter an array into a loop. The initial array formula I used, and entered into the cells directly, was this:

=IFERROR(INDEX(Color!$A:$A, SMALL(IF(ISBLANK(Color!$A:$A)+ISERROR(Color!$A:$A)++IF(Color!$B:$B<>A$1,1,0 ), "", ROW(Color!$A:$A)-MIN(ROW(Color!$A:$A))+1), ROW(1:1))),"")

I would then copy and paste this formula down the column until all the rows with data would be filled, and then I would copy it across the columns (to the right) so all the columns would contain the formula. Obviously, a better way to do this would be to create a loop using VBA, correct? I am very new to VBA and tried to create a code that would do this but I cannot get it to work. This is what I have so far:

[VBA]Sub Color()
Worksheets("Dates").Activate
Range("A2").Select
Do
Selection.FormulaArray = _
"=IFERROR(INDEX(Color!$A:$A,SMALL(IF(ISBLANK(Color!$A:$A)+ISERROR(Color!$A: $A)++IF(Color!$B:$B<>A$1,1,0), """", ROW(Color!$A:$A)-MIN(ROW(Color!$A:$A))+1), ROWS(ActiveCell.Row - 1))),"" "")"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(1, 0))
End Sub[/VBA]

Any suggestions??
Thanks,
Amy

Not tested but i just fixed your formula, give it a go[VBA]Sub Color()

Worksheets("Dates").Activate
Range("A2").Select

Do
Selection.Formula = _
"=IFERROR(INDEX(Color!$A:$A,SMALL(IF(ISBLANK(Color!$A:$A)+ISERROR(Color!$A: $A)++IF(Color!$B:$B<>A$1,1,0), """", ROW(Color!$A:$A)-MIN(ROW(Color!$A:$A))+1), ROWS(" & ActiveCell.Row & " - 1))),"" "")"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(1, 0))

End Sub[/VBA]

Abbab, I think this follows on from what were trying to do:

[VBA]Sub Color()
Worksheets("Dates").Activate
Range("A2").Select
Do
Selection.FormulaArray = _
"=IFERROR(INDEX(Color!$A:$A,SMALL(IF(ISBLANK(Color!$A:$A)+ISERROR(Color!$A: $A)+IF(Color!$B:$B<>A$1,1,0), """", ROW(Color!$A:$A)-MIN(ROW(Color!$A:$A))+1), ROW(ActiveCell)-1)),"" "")"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(1, 0))
End Sub[/VBA]

The ROW and ROWS functions use a range as input rather than a number.

Thank you for the suggestions. I could not get Simon's to work, and with Sassora's the formula was placed in the cell but no value calculated. I have also been trying something like this to no avail as well:

Option Explicit
Sub ColorLoop()


Dim xrow As Integer
Dim xcol As Integer

xrow = 1
xcol = 1

Do Until Cells(xrow, 1) = ""

Cells(xrow + 1, xcol).Formula.Array = "=IFERROR(INDEX(Color!$A$2:$A$500, SMALL(IF(ISBLANK(Color!$A$2:$A$500)+ISERROR(Color!$A$2:$A$500)++IF(Color!$B $2:$B$500<>A$1,1,0), "", ROW(Color!$A$2:$A$500)-MIN(ROW(Color!$A$2:$A$500))+1), ROW())),"") "

xrow = xrow + 1

Loop

End Sub

Any suggestions??
Thanks all!

Can you post the sheet?

Sure, I have attached it! Please let me know if you need me to clarify anything on it!
Thanks!
Amy

What does the formula aim to work out? can you give more background?

If you are looking to find the average difference in time between colours, a simple formula should suffice along the lines of: "((A$52-A$2+1)-I2)/I2".
There is no need to sum all of the differences and then add them up.

Hi Sassora,
Thank you for that formula! I tried it and it seems to work, it is just off a little bit. For example, when I calculate the "black" averages between dates I get 20.67, and the formula produces a result of 20 even, I calculate 21 for the "orange" average and the formula produces a result of 20, etc. Can you explain the discrepancies? Also, since this data will be added to continually, is there a way to set the range so it is not A$52, but continues down the column?
Thanks so much!
Amy

What range are you looking at?

Can you attach a worksheet with calculations?

Hi Sassora,
Please see attached, the first sheet.
Thanks!
Amy

Where are the calculations that give 20.67? and 21?