Consulting

Results 1 to 15 of 15

Thread: Solved: Is there a shortcut for this code?

  1. 06-21-2010, 08:15 PM #1
    genracela
    genracela is offline
    VBAX Contributor
    Joined
    Apr 2010
    Posts
    152
    Location

    Solved: Is there a shortcut for this code?

    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!

    [VBA]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" & lRow).Formula = "=INDEX('[Sample CFTMAL.xls]Sheet1'!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[/VBA]
    Reply With Quote Reply With Quote
  2. 06-22-2010, 12:14 AM #2
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    [vba]

    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
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
  3. 06-22-2010, 12:40 AM #3
    genracela
    genracela is offline
    VBAX Contributor
    Joined
    Apr 2010
    Posts
    152
    Location
    Thanks XLD!!!
    Reply With Quote Reply With Quote
  4. 06-22-2010, 12:40 AM #4
    p45cal
    p45cal is offline
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,875
    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:
    [VBA]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
    [/VBA]
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
    Reply With Quote Reply With Quote
  5. 06-22-2010, 04:39 PM #5
    genracela
    genracela is offline
    VBAX Contributor
    Joined
    Apr 2010
    Posts
    152
    Location
    Thanks p45cal!

    I'll try it too!!!
    Reply With Quote Reply With Quote
  6. 06-22-2010, 06:00 PM #6
    genracela
    genracela is offline
    VBAX Contributor
    Joined
    Apr 2010
    Posts
    152
    Location
    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!
    Reply With Quote Reply With Quote
  7. 06-22-2010, 11:58 PM #7
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    What is the last row of E? What does lRow return? ARe you saying it doesn't populate columns E,F & G?

    Maybe

    [vba]

    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
    [/vba]

    If not, post your workbook.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
  8. 06-24-2010, 12:05 AM #8
    genracela
    genracela is offline
    VBAX Contributor
    Joined
    Apr 2010
    Posts
    152
    Location
    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.
    Reply With Quote Reply With Quote
  9. 06-24-2010, 01:48 AM #9
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Can you post the workbook for us?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
  10. 06-24-2010, 02:12 AM #10
    Aussiebear
    Aussiebear is offline
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,059
    Location
    Perhaps a gap in the data in column E?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link
    Reply With Quote Reply With Quote
  11. 06-24-2010, 06:13 PM #11
    genracela
    genracela is offline
    VBAX Contributor
    Joined
    Apr 2010
    Posts
    152
    Location
    I created a dummy workbook with my formula on it.

    Thanks!
    Reply With Quote Reply With Quote
  12. 06-25-2010, 12:47 AM #12
    p45cal
    p45cal is offline
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,875
    Presuming column E is pre-populated on the Output sheet, the code for your sample workbook is probably:
    [vba]Sub blah()
    Dim lRow As Long
    With Sheets("Output")
    lRow = .Range("E:E").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
    .Range("A7" & lRow & ",F7:I" & lRow).FormulaR1C1 = "=INDEX(Data!C,MATCH(RC5,Data!C5,0))"
    End With
    End Sub
    [/vba]
    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:
    [vba]Sub blah2()
    Dim lRow As Long
    lRow = Range("E:E").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
    Range("A7" & lRow & ",F7:Z" & lRow).FormulaR1C1 = "=INDEX('[Sample CFTMAL.xls]Sheet1'!C,MATCH(RC5,'[Sample CFTMAL.xls]Sheet1'!C5,0))"
    End Sub
    [/vba]Have I got this right?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
    Reply With Quote Reply With Quote
  13. 06-27-2010, 06:24 PM #13
    genracela
    genracela is offline
    VBAX Contributor
    Joined
    Apr 2010
    Posts
    152
    Location
    Hi XLD!

    It's perfect! Thank you so much!
    Reply With Quote Reply With Quote
  14. 06-28-2010, 12:19 AM #14
    p45cal
    p45cal is offline
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,875
    xld?!

    Quote Originally Posted by genracela
    Hi XLD!

    It's perfect! Thank you so much!
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
    Reply With Quote Reply With Quote
  15. 06-28-2010, 05:16 PM #15
    genracela
    genracela is offline
    VBAX Contributor
    Joined
    Apr 2010
    Posts
    152
    Location
    Oooppps sorry, pasted the wrong name!

    Thanks p45cal! The codes are perfect!
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules