+ Reply to Thread
Results 1 to 3 of 3

VBA for creating Index-Match every 2 rows where INDEX refer to a different column per row

  1. #1
    Registered User
    Join Date
    11-20-2012
    Location
    Tokyo, Japan
    MS-Off Ver
    Excel 2007
    Posts
    2

    VBA for creating Index-Match every 2 rows where INDEX refer to a different column per row

    Hi,

    I am a newbie to VBA programming so I would like to apologize in advance if my simple sub below is so elementary.

    I would like to ask for advise on how to make the sub below more efficient.

    I am trying to INDEX MATCH the values from another worksheet (RTM) into my Scorecard worksheet. The formula should be the same except for I would like my INDEX to refer to the next column of the RTM worksheet everytime I move down 2 rows on the main worksheet.

    I hope my code below helps explain this further.


    Sub Scorecard()

    Worksheets("Scorecard").Activate

    Cells(4, 9).FormulaR1C1 = "=IF(ISERROR(INDEX(RTM!C43,MATCH(Scorecard!R2C,RTM!C1,0))),"""",INDEX(RTM!C43,MATCH(Scorecard!R2C,RTM!C1,0)))"
    Cells(4, 9).AutoFill Destination:=Range(Cells(4, 9), Cells(4, 52))

    Cells(6, 9).FormulaR1C1 = "=IF(ISERROR(INDEX(RTM!C44,MATCH(Scorecard!R2C,RTM!C1,0))),"""",INDEX(RTM!C44,MATCH(Scorecard!R2C,RTM!C1,0)))"
    Cells(6, 9).AutoFill Destination:=Range(Cells(6, 9), Cells(6, 52))

    Cells(8, 9).FormulaR1C1 = "=IF(ISERROR(INDEX(RTM!C45,MATCH(Scorecard!R2C,RTM!C1,0))),"""",INDEX(RTM!C45,MATCH(Scorecard!R2C,RTM!C1,0)))"
    Cells(8, 9).AutoFill Destination:=Range(Cells(8, 9), Cells(8, 52))

    End Sub

    Any assistance any of you can give is much appreciated!
    Thank you!

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,072

    Re: VBA for creating Index-Match every 2 rows where INDEX refer to a different column per

    Try something like this...

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-20-2012
    Location
    Tokyo, Japan
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: VBA for creating Index-Match every 2 rows where INDEX refer to a different column per

    Thank you so much for the prompt response! It worked!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1