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,

 

[Vin90]

How exactly does defining any variable change the formatting of anything?

Hi @MARK858, The format as in look of my data table, because I have Title in row 1. So the old code replaces that row. Which due to my lack of knowledge in the Dim lrow as long,etc.

So by using this code:

Dim lrow As Long
lrow = Worksheets("RefinedData").Range("A" & Rows.Count).End(xlUp).Row

Does it mean that excel will find the first empty cell in column A? And will fill it with whatever formula or action that we assign to it?

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.

Is there any way around to make it possible so that it can cover the below 3?

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

The above code works perfect, yet I need a small suggestion because I realized that this part (bottom code) of my formula was wrong:

.Formula = "=IFERROR(IF(ROWS(A$3:A3)>$A$1,"""",INDEX([I][COLOR=#ff0000][U]RawData!A:A[/U][/COLOR][/I],SMALL(IF(ISNUMBER(ID),ID),ROWS(A$3:A3)))),"""")"

Hence, I need to adjust it to a group name, and thus affecting the range

With Worksheets("RefinedData").Range("[COLOR=#ff0000]A3:E[/COLOR]" & lrow)

I tried to adjust and run this code instead:

Sub Opt2()
Dim lrow As Long
lrow = Worksheets("RefinedData").Range("AP1").Value
    With Worksheets("RefinedData").Range("A2:A" & lrow)
        .Formula = "=IFERROR(IF(ROWS($A$2:A2)>$A$1,"""",INDEX(PRD,SMALL(IF(ISNUMBER(ID),ID),ROWS($A$2:A2)))),"""")"
        .FormulaArray = .FormulaR1C1
    End With
End Sub

It works well for the values in column A, but now I need to expand this code to the column B,C,D,...,AO where each column has a slightly different formula.
Is there something that I can do with it?