Simplify with Autofill Code

Vin90

New Member
Hi, I'm writing some code which is quite repetitive because I have a data range from column A:AO

This is how I wrote my code:
Code:
``````Sub UpdateRefineData()

Worksheets("RefinedData").Select

Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
Range("A3:A" & lrow).FormulaArray = "=IFERROR(IF(ROWS(A\$3:A3)>\$A\$1,"""",INDEX(RawData!A:A,SMALL(IF(ISNUMBER(ID),ID),ROWS(A\$3:A3)))),"""")"
Range("B3:B" & lrow).FormulaArray = "=IFERROR(IF(ROWS(B\$3:B3)>\$A\$1,"""",INDEX(RawData!B:B,SMALL(IF(ISNUMBER(ID),ID),ROWS(B\$3:B3)))),"""")"
Range("C3:C" & lrow).FormulaArray = "=IFERROR(IF(ROWS(C\$3:C3)>\$A\$1,"""",INDEX(RawData!C:C,SMALL(IF(ISNUMBER(ID),ID),ROWS(C\$3:C3)))),"""")"
.
.
.
Range("AO3:AO" & lrow).FormulaArray = "=IFERROR(IF(ROWS(AO\$3:AO3)>\$A\$1,"""",INDEX(RawData!C:C,SMALL(IF(ISNUMBER(ID),ID),ROWS(AO\$3:AO3)))),"""")"
End Sub``````
Way too long, and prone to mistakes.
Is there a way to simplify this? I'm thinking the AutoFill might work, because that's how I've done it manually.

Cheers,

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Rick Rothstein

MrExcel MVP
Hi, I'm writing some code which is quite repetitive because I have a data range from column A:AO

This is how I wrote my code:
Code:
``````Sub UpdateRefineData()

Worksheets("RefinedData").Select

Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
[B][COLOR="#FF0000"]Range("A3:A" & lrow).FormulaArray = "=IFERROR(IF(ROWS(A\$3:A3)>\$A\$1,"""",INDEX(RawData!A:A,SMALL(IF(ISNUMBER(ID),ID),ROWS(A\$3:A3)))),"""")"
Range("B3:B" & lrow).FormulaArray = "=IFERROR(IF(ROWS(B\$3:B3)>\$A\$1,"""",INDEX(RawData!B:B,SMALL(IF(ISNUMBER(ID),ID),ROWS(B\$3:B3)))),"""")"
Range("C3:C" & lrow).FormulaArray = "=IFERROR(IF(ROWS(C\$3:C3)>\$A\$1,"""",INDEX(RawData!C:C,SMALL(IF(ISNUMBER(ID),ID),ROWS(C\$3:C3)))),"""")"
.
.
.
Range("AO3:AO" & lrow).FormulaArray = "=IFERROR(IF(ROWS(AO\$3:AO3)>\$A\$1,"""",INDEX(RawData!C:C,SMALL(IF(ISNUMBER(ID),ID),ROWS(AO\$3:AO3)))),"""")"[/COLOR][/B]
End Sub``````
Way too long, and prone to mistakes.
Is there a way to simplify this? I'm thinking the AutoFill might work, because that's how I've done it manually.
First off, according to the documentation, your formula must be in R1C1 notation, not A1 notation like your above code shows. Now, I believe all of what I have highlighted in red above can be replaced by this single line of code...
Code:
``````[table="width: 500"]
[tr]
[td]Range("A3:AO" & lrow).FormulaArray = "=IFERROR(IF(ROWS(R3C:RC)>R1C1,"""",INDEX(RawData!C,SMALL(IF(ISNUMBER(ID),ID),ROWS(R3C:RC)))),"""")"[/td]
[/tr]
[/table]``````

Vin90

New Member
First off, according to the documentation, your formula must be in R1C1 notation, not A1 notation like your above code shows. Now, I believe all of what I have highlighted in red above can be replaced by this single line of code...
Code:
``````[TABLE="width: 500"]
<tbody>[TR]
[TD]Range("A3:AO" & lrow).FormulaArray = "=IFERROR(IF(ROWS(R3C:RC)>R1C1,"""",INDEX(RawData!C,SMALL(IF(ISNUMBER(ID),ID),ROWS(R3C:RC)))),"""")"[/TD]
[/TR]
</tbody>[/TABLE]``````
I have a COUNT that is used as a reference point at R1C1(highlighted below)

Manually, the formula that I want to put in column A3 is:
{=IFERROR(IF(ROWS(A\$3:A3)>\$A\$1,"""",INDEX(RawData!A:A,SMALL(IF(ISNUMBER(ID),ID),ROWS(A\$3:A3)))),"""")}

And I will manually autofill down the column and across, so in column B the formula changes slightly to (and so on until column AO)
{=IFERROR(IF(ROWS(B\$3:B3)>\$A\$1,"""",INDEX(RawData!B:B,SMALL(IF(ISNUMBER(ID),ID),ROWS(B\$3:B3)))),"""")}
...
{=IFERROR(IF(ROWS(AO\$3:AO3)>\$A\$1,"""",INDEX(RawData!AO:AO,SMALL(IF(ISNUMBER(ID),ID),ROWS(AO\$3:AO3)))),"""")}

When I run this vba code, I get the first row values correctly, but the subsequent rows are copy of row 1.
Code:
``````Sub UpdateRefineData()
Worksheets("RefinedData").Select
Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
Range("A3:A" & lrow).FormulaArray = "=IFERROR(IF(ROWS(A\$3:A3)>\$A\$1,"""",INDEX(RawData!A:A,SMALL(IF(ISNUMBER(ID),ID),ROWS(A\$3:A3)))),"""")"
Range("B3:B" & lrow).FormulaArray = "=IFERROR(IF(ROWS(B\$3:B3)>\$A\$1,"""",INDEX(RawData!B:B,SMALL(IF(ISNUMBER(ID),ID),ROWS(B\$3:B3)))),"""")"
...
Range("AO3:AO" & lrow).FormulaArray = "=IFERROR(IF(ROWS(AO\$3:AO3)>\$A\$1,"""",INDEX(RawData!C:C,SMALL(IF(ISNUMBER(ID),ID),ROWS(AO\$3:AO3)))),"""")"
End Sub``````
I tried the suggested code and it returned as a single value from A3:AO

Rick Rothstein

MrExcel MVP
I tried the suggested code and it returned as a single value from A3:AO
I would expect the cells to display a value (that is what a formula does)... but did it put the array formula in the cells themselves?

Vin90

New Member
I would expect the cells to display a value (that is what a formula does)... but did it put the array formula in the cells themselves?
The code that you provided did result in an array formula, but it's correct only for single cell (R1C1) where the formula is expected, the rest are wrong simply due to the data range is not adapting.

Code:
``{=IFERROR(IF(ROWS(A\$3:A3)>\$A\$1,"",INDEX(RawData!A:A,SMALL(IF(ISNUMBER(ID),ID),ROWS(A\$3:A3)))),"")}``
This is the formula found in all cells ranged from A3:AO

Last edited:

MARK858

MrExcel MVP
@Vin90, what do you get with...

Sub Opt2()
Code:
``````Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
With Sheet1.Range("A3:AO" & lrow)
.Formula = "=IFERROR(IF(ROWS(A\$3:A3)>\$A\$1,"""",INDEX(RawData!A:A,SMALL(IF(ISNUMBER(ID),ID),ROWS(A\$3:A3)))),"""")"
.FormulaArray = .FormulaR1C1
End With
End Sub``````

Rick Rothstein

MrExcel MVP
@Vin90, what do you get with...

Sub Opt2()
Code:
``````Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
With Sheet1.Range("A3:AO" & lrow)
.Formula = "=IFERROR(IF(ROWS(A\$3:A3)>\$A\$1,"""",INDEX(RawData!A:A,SMALL(IF(ISNUMBER(ID),ID),ROWS(A\$3:A3)))),"""")"
.FormulaArray = .FormulaR1C1
End With
End Sub``````
@MARK858... Good job! That looks like it works very well.

@Vin90... MARK858 has got the solution for you in Message #6 .

Last edited:

MARK858

MrExcel MVP
@Vin90, what do you get with...
It would help if I used the OP's sheet name and put the code tags in the right place though :banghead:

Code:
``````Sub Opt2()
Dim lrow As Long
lrow = Worksheets("RefinedData").Range("A" & Rows.Count).End(xlUp).Row
With Worksheets("RefinedData").Range("A3:AO" & lrow)
.Formula = "=IFERROR(IF(ROWS(A\$3:A3)>\$A\$1,"""",INDEX(RawData!A:A,SMALL(IF(ISNUMBER(ID),ID),ROWS(A\$3:A3)))),"""")"
.FormulaArray = .FormulaR1C1
End With
End Sub``````

Last edited:

Vin90

New Member
It would help if I used the OP's sheet name and put the code tags in the right place though :banghead:

Code:
``````Sub Opt2()
Dim lrow As Long
lrow = Worksheets("RefinedData").Range("A" & Rows.Count).End(xlUp).Row
With Worksheets("RefinedData").Range("A3:AO" & lrow)
.Formula = "=IFERROR(IF(ROWS(A\$3:A3)>\$A\$1,"""",INDEX(RawData!A:A,SMALL(IF(ISNUMBER(ID),ID),ROWS(A\$3:A3)))),"""")"
.FormulaArray = .FormulaR1C1
End With
End Sub``````
Hi @MARK858, I may have a wrong understanding of the Dim lrow As Long..
When I tried it on my empty fresh data sheet it screwed up the format..

My fresh data sheet looks something like this:
 A B C D E 1 n 2 Product Date Total Product Good Product Bad Product 3 4

<tbody>
</tbody>

When I run the given code on this kind of sheet it changes the value of A2:AO2, and put the same values on A3:AO3.
the "n" in A1 is a count that is based on the source sheet (RawData!)

So I'm thinking a different way to approach the problem:
1. Create macro that fill the 1st row with array formulas.
Code:
``````Sub UpdateRefineData()
Worksheets("RefinedData").Range("A3").FormulaArray = "=IFERROR(IF(ROWS(\$A\$3:\$A3)>\$A\$1,"""",INDEX(PRD,SMALL(IF(ISNUMBER(ID),ID),ROWS(\$A\$3:\$A3)))),"""")"
Worksheets("RefinedData").Range("B3").FormulaArray = "=IFERROR(IF(ROWS(\$B\$3:\$B3)>\$A\$1,"""",INDEX(DTE,SMALL(IF(ISNUMBER(ID),ID),ROWS(\$B\$3:\$B3)))),"""")"
...
'all the way to row AO
End Sub``````
2. I need a macro code that will help to basically copy down those formula to the next n-row (A1)

I think this approach is better to ensure that every time I run the macro the 1st row acts as the anchoring point for the formula. Any suggestions? Thank you.

MARK858

MrExcel MVP
Hi @MARK858, I may have a wrong understanding of the Dim lrow As Long..
When I tried it on my empty fresh data sheet it screwed up the format..
How exactly does defining any variable change the formatting of anything?

When I run the given code on this kind of sheet it changes the value of A2:AO2
That is because it was looking for the last row in Column A as per your original code (and was incorrect so because there were only 2 lines the range was A2:AO3).

If you want your last row based on the cell value in A1 then all you do is the below and the value in A1 can't be below 3 as that is the row number where your data starts.

Code:
``````Sub Opt2()
Dim lrow As Long
lrow = Worksheets("RefinedData").Range("A1").Value
With Worksheets("RefinedData").Range("A3:E" & lrow)
.Formula = "=IFERROR(IF(ROWS(A\$3:A3)>\$A\$1,"""",INDEX(RawData!A:A,SMALL(IF(ISNUMBER(ID),ID),ROWS(A\$3:A3)))),"""")"
.FormulaArray = .FormulaR1C1
End With
End Sub``````

Last edited: