none
Excel Index/Match Question RRS feed

  • Question

  • Any idea why this bit of code is returning a "Sub or Function not defined" on the first Match call? I am a total VBA newbie, so I know it is something simple, but after an hour of searching and trying different things, no luck:

    Do
            Range("E" & X).Select
            ActiveCell.Formula = (Index("D1:D250", Match("C & X", "C1:C250", "0"))) - (Index("G1:G250", Match("C & X", "G1:G250", "0")))
           
            X = X + 1
           
    Loop Until X > RowCCount

    Both X and RowCCount are defined earlier in the macro.

    Tuesday, January 17, 2017 8:51 PM

Answers

  • Replace

            Range("E" & X).Select
            ActiveCell.Formula = (Index("D1:D250", Match("C & X", "C1:C250", "0"))) - (Index("G1:G250", Match("C & X", "G1:G250", "0")))

    with

            Range("E" & X).Formula = "=INDEX(D1:D250,MATCH(C" & X & _
                ",C1:C250,0))-INDEX(G1:G250,MATCH(C" & X & ",G1:G250,0))"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, January 17, 2017 9:07 PM

All replies

  • Replace

            Range("E" & X).Select
            ActiveCell.Formula = (Index("D1:D250", Match("C & X", "C1:C250", "0"))) - (Index("G1:G250", Match("C & X", "G1:G250", "0")))

    with

            Range("E" & X).Formula = "=INDEX(D1:D250,MATCH(C" & X & _
                ",C1:C250,0))-INDEX(G1:G250,MATCH(C" & X & ",G1:G250,0))"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, January 17, 2017 9:07 PM
  • Hi 3knuckleshuffle,

    Thanks for visiting our forum.

    Then this forum mainly focus on questions and feedback related to Excel client. Since your issue is more related to developing issues involving Excel, I would move this thread into the following dedicated MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thank you for your understanding.

    Best regards,
     Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Wednesday, January 18, 2017 5:32 AM
  • Thanks Hans, with some edits, I think I have everything working. Your help is much appreciated.
    Wednesday, January 18, 2017 1:42 PM
  • Thank you for the info Yuki, noted for later.
    Wednesday, January 18, 2017 1:42 PM