genracela
06-21-2010, 08:15 PM
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
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