Simplify with Autofill Code

[Vin90]

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:

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,

 

[Rick Rothstein]

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:

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

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

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

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

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]

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]

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.

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

 

[MARK858]

@Vin90, what do you get with...

Sub Opt2()

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]

@Vin90, what do you get with...

Sub Opt2()

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 .

 

[MARK858]

@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

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

 

[Vin90]

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

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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]n[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Product[/TD]
[TD]Date[/TD]
[TD]Total Product[/TD]
[TD]Good Product[/TD]
[TD]Bad Product[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

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]

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.

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