Solved: Is there a shortcut for this code?

[genracela]

I'm just wondering, since all of my coulmns have the same formula, is there a shortcut for this?

The only criteria that is changing is the index array. If range is at A coulmn then the index array is also at A column.

Thanks!

Sub CFTMAL()
Dim lRow As Long

lRow = Range("A:A").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, _
searchdirection:=xlPrevious).Row
Range("A7:A" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!A:A,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("B7:B" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!B:B,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("C7:C" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!C:C,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("D7:D" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!D:D,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("E7:E" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!E:E,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("F7:F" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!F:F,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("G7:G" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!G:G,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("H7:H" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!H:H,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("I7:I" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!I:I,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("J7:J" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!J:J,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("K7:K" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!K:K,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("L7:L" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!L:L,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("M7:M" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!M:M,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("N7:N" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!N:N,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("O7:O" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!O:O,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("P7:P" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!P:P,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("Q7:Q" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!Q:Q,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("R7:R" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!R:R,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("S7:S" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!S:S,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("T7:T" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!T:T,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("U7:U" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!U:U,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("V7:V" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!V:V,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("W7:W" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!W:W,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("X7:X" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!X:X,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("Y7:Y" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!Y:Y,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
Range("Z7:Z" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!Z:Z,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
End sub

[Bob Phillips]

Sub CFTMAL()
Dim lRow As Long

lRow = Range("A:A").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, _
searchdirection:=xlPrevious).Row
Range("A7").Resize(lRow, 26).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!A:A,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
End Sub

[genracela]

Thanks XLD!!!

[p45cal]

Oh well, having done the work, xld beat me to it, but I'll post anyway, it's slightly different; xld's places the formulae in a different range:
Sub blah()
Dim lRow As Long
lRow = Range("A:A").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
Range("A7:A" & lRow).Resize(, 26).FormulaR1C1 = "=INDEX('[Sample CFTMAL.xls]Sheet1'!C,MATCH(RC5,'[Sample CFTMAL.xls]Sheet1'!C5,0))"
End Sub

[genracela]

Thanks p45cal!

I'll try it too!!!

[genracela]

I tried both of the code.

The code from XLD is okay, but it only populate A7 to D12 and H7 to Z12

I need to populate all rows and my criteria is at E7 until the last row of E.

Thanks!

[Bob Phillips]

What is the last row of E? What does lRow return? ARe you saying it doesn't populate columns E,F & G?

Maybe



Sub CFTMAL()
Dim lRow As Long

lRow = Range("A:A").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, _
searchdirection:=xlPrevious).Row
Range("A7").Resize(lRow + 6, 26).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!A:A,MATCH($E7,'[Sample CFTMAL.xls]Sheet1'!$E:$E,0))"
End Sub


If not, post your workbook.

[genracela]

It does populate E F and G but, it sometimes stopped in the middle of the file.

Example, my file is until row 2425, but it only calculate until row 23.

It doesn't continue till the last row.

[Bob Phillips]

Can you post the workbook for us?

[Aussiebear]

Perhaps a gap in the data in column E?

[genracela]

I created a dummy workbook with my formula on it.

Thanks!

[p45cal]

Presuming column E is pre-populated on the Output sheet, the code for your sample workbook is probably:
Sub blah()
Dim lRow As Long
With Sheets("Output")
lRow = .Range("E:E").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
.Range("A7:D" & lRow & ",F7:I" & lRow).FormulaR1C1 = "=INDEX(Data!C,MATCH(RC5,Data!C5,0))"
End With
End Sub

Which for your original question translates to the following, but first make sure that the active sheet is the one that you want the formulae to appear on and that its column E is populated:
Sub blah2()
Dim lRow As Long
lRow = Range("E:E").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
Range("A7:D" & lRow & ",F7:Z" & lRow).FormulaR1C1 = "=INDEX('[Sample CFTMAL.xls]Sheet1'!C,MATCH(RC5,'[Sample CFTMAL.xls]Sheet1'!C5,0))"
End Sub
Have I got this right?

[genracela]

Hi XLD!

It's perfect! Thank you so much!

[p45cal]

xld?!


Hi XLD!

It's perfect! Thank you so much!

[genracela]

Oooppps sorry, pasted the wrong name!:bug:

Thanks p45cal! The codes are perfect!