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.

 

[johnnyL]

@Kenor, you keep saying the same thing over and over.

You didn't say what you consider is wrong with my last post.

 

[Kenor]

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

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

Hi @MARK858

I have try your formula.

The result almost achieved what I wanted..

But formulas can be copied to each row when there is data in the adjacent column only.

Can you make it automatically copied even if there is no data?

And fyi, this formula also need to press Ctrl+Shift+Enter to complete the formula because use array formulas.

When press Ctrl+Shift+Enter

But it seems that the formula is unable to detect the correct data from the ‘Register OUT’ sheet.

 

[Kenor]

I think need to do some amendment on the VBA code.

Because the result shows as below;
(The formula captured the data from 'Register OUT' sheet)

G3 =

G4 =

G5 =

 

[Kenor]

@Kenor, you keep saying the same thing over and over.

You didn't say what you consider is wrong with my last post.

Sorry @johnnyL ?

I didn't change anything with your code.

I just copy and paste. But when I run it, the results show like that.

I'll try again.. maybe I should delete everything and start over.

 

[Kenor]

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

This is result when I try this code..

View attachment 59912

Yeahhh... almost done!

Your formula also work well.

Sorry, after I delete all and start again. Then, it's work!

But need help to run the formula by pressing ctrl-shift-enter

 

[johnnyL]

@Kenor, If you are addressing me in your last post:

Sub TestArrayFormulasCopyDown()
'
    Dim LastRowInColumn As Long
'
    LastRowInColumn = Range("C" & Rows.Count).End(xlUp).Row                                 ' Returns the Last Row Number used of the C column
'
    Range("G2").FormulaArray = "=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

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)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Kenor]

Yess...finally!

Thanks a lot @johnnyL

But maybe I need to create 1 button to run the formula.

Based on current practice, after I transfer the IN(Kg) data into the Database sheet.
I need to open the Visual Basic editor to run the formula.

Anyway, thank you again @johnnyL for your efforts to resolve this matter.

 

[johnnyL]

You are welcome @Kenor, I see you have altered the code again, as long as you are happy though.