PDA

View Full Version : Help with Loop



abbab
04-12-2013, 09:18 AM
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:

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

Any suggestions??
Thanks,
Amy

Simon Lloyd
04-12-2013, 04:55 PM
Not tested but i just fixed your formula, give it a goSub 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

sassora
04-14-2013, 01:59 AM
Abbab, I think this follows on from what were trying to do:

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

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

abbab
04-16-2013, 10:01 AM
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
04-16-2013, 10:35 AM
Can you post the sheet?

abbab
04-17-2013, 06:11 AM
Sure, I have attached it! Please let me know if you need me to clarify anything on it!
Thanks!
Amy

sassora
04-20-2013, 05:51 AM
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
05-02-2013, 06:00 AM
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
05-03-2013, 02:37 PM
What range are you looking at?

Can you attach a worksheet with calculations?

abbab
05-06-2013, 07:02 AM
Hi Sassora,
Please see attached, the first sheet.
Thanks!
Amy

sassora
05-06-2013, 07:07 AM
Where are the calculations that give 20.67? and 21?