Help with Loop

[abbab]

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

[Simon Lloyd]

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]

[sassora]

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.

[abbab]

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!

[sassora]

Can you post the sheet?

[abbab]

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

[sassora]

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.

[abbab]

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

[sassora]

What range are you looking at?

Can you attach a worksheet with calculations?

[abbab]

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

[sassora]

Where are the calculations that give 20.67? and 21?