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

Kenor · Mar 9, 2022

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.

johnnyL · Mar 11, 2022

Post #7 shows the A1 style formula to put in G2. Post #8 shows the code to drag that A1 style formula down the page.

I don't like R1C1 style so I won't be much help there.

A1 style makes more sense to me.

Kenor · Mar 11, 2022

johnnyL said:
Post #7 shows the A1 style formula to put in G2. Post #8 shows the code to drag that A1 style formula down the page.

I don't like R1C1 style so I won't be much help there.

A1 style makes more sense to me.

If you can help me with A1 style also ok for me..

I just looking for any formula can copy and paste as I mention in post #3.

I have no idea how to edit my formula or create the new one.

johnnyL · Mar 11, 2022

I think this is what you are asking for:

VBA Code:

Sub TestFormulasCopyDown()
'
    Dim LastRowInColumn As Long
'
    LastRowInColumn = Range("C" & Rows.Count).End(xlUp).Row                                 ' Returns the Last Row Number used of the C column
'
    Range("G2").Formula = "=IFERROR(INDEX('Register OUT'!$A$3:$K$" & LastRowInColumn & _
            ", MATCH(1,(DataBase!$C2='Register OUT'!$C$3:$C$" & LastRowInColumn & _
            ")*(DataBase!$D2='Register OUT'!$D$3:$D$" & LastRowInColumn & _
            ")*(DataBase!$E2='Register OUT'!$E$3:$E$" & LastRowInColumn & _
            ")*(DataBase!$I2='Register OUT'!$I$3:$I$" & LastRowInColumn & "),0),7),0)"      ' Copy formula to be used to Range("G2")
    Range("G2").AutoFill Destination:=Range("G2:G" & LastRowInColumn), Type:=xlFillDefault  ' Fill the formula from G2 down the G column
End Sub

johnnyL · Mar 11, 2022

Kenor said:
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.

Just a cleanup footnote: upon further investigation, I think the 'R3C' I mentioned earlier may have been acceptable for excel. Like I said, I don't like that style of coding, (R1C1), it requires extra math that I don't care to use, if not needed. From my reading, 'R3C' apparently equates to 'R3C1'. So in other words, I was apparently wrong in thinking something was missing, apparently Excel assumes the last value. My appollogies.

Kenor · Mar 13, 2022

johnnyL said:

I think this is what you are asking for:

VBA Code:

Sub TestFormulasCopyDown()
'
    Dim LastRowInColumn As Long
'
    LastRowInColumn = Range("C" & Rows.Count).End(xlUp).Row                                 ' Returns the Last Row Number used of the C column
'
    Range("G2").Formula = "=IFERROR(INDEX('Register OUT'!$A$3:$K$" & LastRowInColumn & _
            ", MATCH(1,(DataBase!$C2='Register OUT'!$C$3:$C$" & LastRowInColumn & _
            ")*(DataBase!$D2='Register OUT'!$D$3:$D$" & LastRowInColumn & _
            ")*(DataBase!$E2='Register OUT'!$E$3:$E$" & LastRowInColumn & _
            ")*(DataBase!$I2='Register OUT'!$I$3:$I$" & LastRowInColumn & "),0),7),0)"      ' Copy formula to be used to Range("G2")
    Range("G2").AutoFill Destination:=Range("G2:G" & LastRowInColumn), Type:=xlFillDefault  ' Fill the formula from G2 down the G column
End Sub

I already try this code, but sorry still can't solve..?

This is result when I try this code..

Kenor · Mar 13, 2022

Sorry, if my description is difficult to understand.

I actually want to copy the formula from G2 down the G column.

But the formula will always change according to the row number.

G2 =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)

On this table,
I want to make the OUT(Kg) data will appear in column G (in the Database sheet) when the IN(Kg) data can be captured in the 'Register OUT' sheet.

This is an example of how it happens when the formula is copied in each row in column G.

The same formula when copied to the next row in column G (from G2 to G3) will be as below =
(Row number for column C, D, E & I which highlighted with Orange color also need to be change) --> So, if the data (F3) can be captured in the ‘Register EXIT’ sheet, automatically the same data will be displayed on the G3.

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

Hope this explanation, will make you more understand.

This is what I want. Formula to be copied to each row up to the last row using vba code.

Thanks.

johnnyL · Mar 13, 2022

I'm not sure what you are doing to get the formula into G1. The code I supplied doesn't do that.

The following is an example from running the code I submitted:

Book1

A

B

C

D

E

F

G

H

1

2

C2

0

3

C3

0

4

C4

0

5

C5

0

6

C6

0

7

C7

0

8

C8

0

9

C9

0

10

C10

0

11

Sheet1

Cell Formulas
Range	Formula
G2:G10	G2	=IFERROR(INDEX('Register OUT'!$A$3:$K$10, MATCH(1,(DataBase!$C2='Register OUT'!$C$3:$C$10)(DataBase!$D2='Register OUT'!$D$3:$D$10)(DataBase!$E2='Register OUT'!$E$3:$E$10)*(DataBase!$I2='Register OUT'!$I$3:$I$10),0),7),0)

Kenor · Mar 13, 2022

G1 only for Title.

The formula starts at G2.

G2 =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)

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

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

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

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

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

and so on....

But I want this formula copied down column G by VBA code.

MARK858 · Mar 13, 2022

@Kenor your main issue is you have made your row numbers that you want to increment absolute in your formula, you can't increment them when they are absolute. Try....

VBA Code:

Sub Kenor()
Sheets("Database").Range("G2:G" & Sheets("Database").Range("C" & Rows.Count).End(xlUp).Row).Formula = "=IFERROR(INDEX('Register OUT'!$A3:$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)"
End Sub

MARK858 · Mar 13, 2022

Or the below if you want the R1C1 version

VBA Code:

Sub Kenor2()
Sheets("Database").Range("G2:G" & Sheets("Database").Range("C" & Rows.Count).End(xlUp).Row).FormulaR1C1 = _
        "=IFERROR(INDEX('Register OUT'!R[1]C1:R2000C11,MATCH(1,(Database!RC3=@'Register OUT'!R3C3:R2000C3)*(Database!RC4=@'Register OUT'!R3C4:R2000C4)*(Database!RC5=@'Register OUT'!R3C5:R2000C5)*(Database!RC9=@'Register OUT'!R3C9:R2000C9),0),7),0)"
End Sub

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

Kenor

Board Regular

johnnyL

Well-known Member

Excel Facts

Kenor

Board Regular

johnnyL

Well-known Member

johnnyL

Well-known Member

Kenor

Board Regular

Kenor

Board Regular

johnnyL

Well-known Member

Kenor

Board Regular

MARK858

MrExcel MVP

MARK858

MrExcel MVP

Forum statistics

Share this page

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

Board Regular

Well-known Member

Excel Facts

Board Regular

Well-known Member

Well-known Member

Board Regular

Board Regular

Well-known Member

Board Regular

MrExcel MVP

MrExcel MVP

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock