How to autofill the formulas in the column until the last row

[Kenor]

Hi All,

I need help to edit below formula.

I want to autofill the formulas starting with the IFERROR (G2) in the column G until the last row that contains the data in the cells next to it (Column C, D, E & I).

Column C,D, E & I is the cells that the IFERROR refers to.



Sub fill_all_column()

' Fill the range G2 till EndRow

Dim rg As Range

Dim cll As Range


Set rg = Range("G2:EndRow")


For Each cll In rg

cll.FormulaR1C1 = "=IFERROR(INDEX('Register OUT'!R3C:R2000C11,MATCH(1,(DataBase!R2C3='Register OUT'!R3C3:R2000C3)*(DataBase!R2C4='Register OUT'!R3C4:R2000C4)*(DataBase!R2C5='Register OUT'!R3C5:R2000C5)*(DataBase!R2C9='Register OUT'!R3C9:R2000C9),0),7),0)"

End Sub


Highlighted with red color need to change depends on rows number.


Thank you.

 

[gordsky]

try this

Sub fill_all_column()

' Fill the range G2 till EndRow

Dim rg As Range

Dim cll As Range


Set rg = Range("G2:G" & cells(rows.count,4).end(xlup).row)
rg.filldown
end sub

 

[Kenor]

try this

Sub fill_all_column()

' Fill the range G2 till EndRow

Dim rg As Range

Dim cll As Range


Set rg = Range("G2:G" & cells(rows.count,4).end(xlup).row)
rg.filldown
end sub

I already try above code but become like this..

I actually want the formula in cell G2 to copy to each cells until the last row.
FormulaR1C1 for G2 = "=IFERROR(INDEX('Register OUT'!R3C:R2000C11,MATCH(1,(DataBase!R2C3='Register OUT'!R3C3:R2000C3)*(DataBase!R2C4='Register OUT'!R3C4:R2000C4)*(DataBase!R2C5='Register OUT'!R3C5:R2000C5)*(DataBase!R2C9='Register OUT'!R3C9:R2000C9),0),7),0)"

But, the row number in the formula above (highlighted with red color) will be changed when copying to each cell.

For example =

G3 = "=IFERROR(INDEX('Register OUT'!R3C:R2000C11,MATCH(1,(DataBase!R3C3='Register OUT'!R3C3:R2000C3)*(DataBase!R3C4='Register OUT'!R3C4:R2000C4)*(DataBase!R3C5='Register OUT'!R3C5:R2000C5)*(DataBase!R3C9='Register OUT'!R3C9:R2000C9),0),7),0)"

G4 = "=IFERROR(INDEX('Register OUT'!R3C:R2000C11,MATCH(1,(DataBase!R4C3='Register OUT'!R3C3:R2000C3)*(DataBase!R4C4='Register OUT'!R3C4:R2000C4)*(DataBase!R4C5='Register OUT'!R3C5:R2000C5)*(DataBase!R4C9='Register OUT'!R3C9:R2000C9),0),7),0)"

and so on...

 

[johnnyL]

G3 = "=IFERROR(INDEX('Register OUT'!R3C:R2000C11

Should that be G3 = "=IFERROR(INDEX('Register OUT'!R3C11:R2000C11
?

Something is missing there.

 

[Kenor]

G3 = "=IFERROR(INDEX('Register OUT'!R3C:R2000C11

Should that be G3 = "=IFERROR(INDEX('Register OUT'!R3C11:R2000C11
?

Something is missing there.

Original formula from excel for G2 as below


=IFERROR(INDEX('Register OUT'!$A$3:$K$2000,MATCH(1,(DataBase!$C$2='Register OUT'!$C$3:$C$2000)*(DataBase!$D$2='Register OUT'!$D$3:$D$2000)*(DataBase!$E$2='Register OUT'!$E$3:$E$2000)*(DataBase!$I$2='Register OUT'!$I$3:$I$2000),0),7),0)


FormulaR1C1 = "=IFERROR(INDEX('Register OUT'!R3C:R2000C11,MATCH(1,(DataBase!R2C3='Register OUT'!R3C3:R2000C3)*(DataBase!R2C4='Register OUT'!R3C4:R2000C4)*(DataBase!R2C5='Register OUT'!R3C5:R2000C5)*(DataBase!R2C9='Register OUT'!R3C9:R2000C9),0),7),0)"


Please correct me if I'm wrong.

 

[johnnyL]

FormulaR1C1 = "=IFERROR(INDEX('Register OUT'!R3C1:R2000C11,MATCH(1,(DataBase!R2C3='Register OUT'!R3C3:R2000C3)*(DataBase!R2C4='Register OUT'!R3C4:R2000C4)*(DataBase!R2C5='Register OUT'!R3C5:R2000C5)*(DataBase!R2C9='Register OUT'!R3C9:R2000C9),0),7),0)"


Correction made.

 

[johnnyL]

According to your previous posts in this thread about what you want to change in the original formula ... the original formula for G2 should be:
=IFERROR(INDEX('Register OUT'!$A$3:$K$2000,MATCH(1,(DataBase!$C2='Register OUT'!$C$3:$C$2000)*(DataBase!$D2='Register OUT'!$D$3:$D$2000)*(DataBase!$E2='Register OUT'!$E$3:$E$2000)*(DataBase!$I2='Register OUT'!$I$3:$I$2000),0),7),0)

 

[johnnyL]

Which leads to the following code:

Sub DragDownFormula()
'
    Dim LastRowInColumn     As Long
'
    LastRowInColumn = Range("C" & Rows.Count).End(xlUp).Row                                 ' Returns the Last Row Number used of the C column
    Range("G2").AutoFill Destination:=Range("G2:G" & LastRowInColumn), Type:=xlFillDefault  ' Fills the formula from G2 down the G column
End Sub

 

[Kenor]

Which leads to the following code:

Sub DragDownFormula()
'
    Dim LastRowInColumn     As Long
'
    LastRowInColumn = Range("C" & Rows.Count).End(xlUp).Row                                 ' Returns the Last Row Number used of the C column
    Range("G2").AutoFill Destination:=Range("G2:G" & LastRowInColumn), Type:=xlFillDefault  ' Fills the formula from G2 down the G column
End Sub

Yes..this formula to copy from G2 and paste down the G column.

FormulaR1C1 for G2 = "=IFERROR(INDEX('Register OUT'!R3C1:R2000C11,MATCH(1,(DataBase!R2C3='Register OUT'!R3C3:R2000C3)*(DataBase!R2C4='Register OUT'!R3C4:R2000C4)*(DataBase!R2C5='Register OUT'!R3C5:R2000C5)*(DataBase!R2C9='Register OUT'!R3C9:R2000C9),0),7),0)"

But, the row number in the formula above (highlighted with red color) will be changed when copying to each cell.

For example =

G3 = "=IFERROR(INDEX('Register OUT'!R3C1:R2000C11,MATCH(1,(DataBase!R3C3='Register OUT'!R3C3:R2000C3)*(DataBase!R3C4='Register OUT'!R3C4:R2000C4)*(DataBase!R3C5='Register OUT'!R3C5:R2000C5)*(DataBase!R3C9='Register OUT'!R3C9:R2000C9),0),7),0)"

G4 = "=IFERROR(INDEX('Register OUT'!R3C1:R2000C11,MATCH(1,(DataBase!R4C3='Register OUT'!R3C3:R2000C3)*(DataBase!R4C4='Register OUT'!R3C4:R2000C4)*(DataBase!R4C5='Register OUT'!R3C5:R2000C5)*(DataBase!R4C9='Register OUT'!R3C9:R2000C9),0),7),0)"

and so on...

 

[johnnyL]

Did you look at Post #7 & Post #8?