Question on Row References in VBA

[abbab]

Hi All,
I really apologize for posting what I am guessing is going to be a really stupid question, but I am trying to teach myself VBA and need a hand with something. I have an array formula that goes in several cells. Using what I found online threw several searches, I found the following (which is only a part of the code): MIN(ROW(Color!$A$2:$A$500))+1), ROW(1:1). It works perfectly and when I drag it down, the row reference changes from ROW(1:1) to ROW(2:2), as it should. If I wanted to use VBA to place this array in the cells though, how would I reference the ROW(1:1) and get it to change to ROW(2:2), etc.? Nothing I have tried as worked. Any suggestions would be welcome!
Thank you!
Amy

[SamT]

[VBA]
Dim LR As Long 'Holds the last row number you put the formula in
Dim i As Long
For i = 1 to LR
Cells(i,ColumnReference).Formula = "=MIN(...etc),ROW(" & i & ":" & i & ")"[/VBA]

If the formula goes in many cells, you will get a smaller file size doing the calculations in VBA and putting only the result in the Cell. Youcan to use an Event to trigger the procedure, (AKA, macro.)

[abbab]

Hi Sam,
Thank you so much! How do I do the calculations in VBA, like you mentioned, and just put the results in the cell? I think that is really what I want to do.
Thanks again!
Amy

[SamT]

What is the formula? Include workbook references even if they are not in the cell's formula.

Cell formula on sheet1 example

• =A1+Sheet2!A1

Formula you show me

• =Sheet1!A1+Sheet2!A1

[abbab]

Hi Sam,
This is the full formula:

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

Thanks!
Amy