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
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