Simplify with Autofill Code

Vin90

New Member
Joined
Oct 20, 2017
Messages
29
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,
 

Some videos you may like

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
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
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
Joined
Oct 20, 2017
Messages
29
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
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
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
Joined
Oct 20, 2017
Messages
29
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
Joined
Nov 12, 2010
Messages
12,404
Office Version
365, 2010
Platform
Windows, Mobile
@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
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
@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
Joined
Nov 12, 2010
Messages
12,404
Office Version
365, 2010
Platform
Windows, Mobile
@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: :biggrin:

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
Joined
Oct 20, 2017
Messages
29
It would help if I used the OP's sheet name and put the code tags in the right place though :banghead: :biggrin:

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:
ABCDE
1n
2ProductDateTotal ProductGood ProductBad 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
Joined
Nov 12, 2010
Messages
12,404
Office Version
365, 2010
Platform
Windows, Mobile
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:

Watch MrExcel Video

Forum statistics

Threads
1,095,752
Messages
5,446,289
Members
405,395
Latest member
anilals

This Week's Hot Topics

Top