PDA

View Full Version : Solved: Is there a shortcut for this code?



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

xld
06-22-2010, 12:14 AM
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
06-22-2010, 12:40 AM
Thanks XLD!!!

p45cal
06-22-2010, 12:40 AM
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
06-22-2010, 04:39 PM
Thanks p45cal!

I'll try it too!!!

genracela
06-22-2010, 06:00 PM
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!

xld
06-22-2010, 11:58 PM
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
06-24-2010, 12:05 AM
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.

xld
06-24-2010, 01:48 AM
Can you post the workbook for us?

Aussiebear
06-24-2010, 02:12 AM
Perhaps a gap in the data in column E?

genracela
06-24-2010, 06:13 PM
I created a dummy workbook with my formula on it.

Thanks!

p45cal
06-25-2010, 12:47 AM
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
06-27-2010, 06:24 PM
Hi XLD!

It's perfect! Thank you so much!

p45cal
06-28-2010, 12:19 AM
xld?!


Hi XLD!

It's perfect! Thank you so much!

genracela
06-28-2010, 05:16 PM
Oooppps sorry, pasted the wrong name!:bug:

Thanks p45cal! The codes are perfect!