VBA code for Two dimensional lookup (Rows & columns) with multiple criteria's.

  • So ... to be concrete ...


    Is the next step to deal with Columns AQ to AY in worksheet Display ... and incorporate your 9 Formulas into your existing macro ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • In fact, dealing with columns AQ to AY is the first priority in worksheet Display.

    However, it would also be great if you modified the previous macro or

    added some extra lines of code to increase its speed and performance.;)

  • OK ... understand the priority ...


    Regarding the macro speed and performance ... you have decided to remove :

    Code
    1. 'Application.ScreenUpdating = False
    2. 'Application.Calculation = xlCalculationManual

    which were initially added ... exactly for your objective : improving speed and performance ... !!!:S

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Attached is your test file version 7 ...


    All 9 Formulas have been incorporated into your macro ...


    Please note since you have decided to remove all data, there is no means to check the results produced by these formulas ...


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Really noob in coding :D;(...........tried to modify.... not producing output...any help please?




    All formulas in Display Sheet AQ:AY....edited two references to make the formula work dynamically


    From

    INDEX(JCB!$C$6:$CB$500,MATCH(Display!D9,JCB!$B$6:$B$500,0),MATCH(Summary!$C$8&Summary!$I$8&Summary!$I$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0))


    Changed to

    INDEX(JCB!$C$6:$CB$500,MATCH(Display!D9,JCB!$B$6:$B$500,0),MATCH(Display!$I$3&Display!$AG$4&Summary!$I$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0))



    Code
    1. these references need to be made as As long (for all formula)
    2. c = Evaluate("=INDEX(JCB!C6:CB500,MATCH(Display!D" & i & ",JCB!B6:B500,0),MATCH(Display!I3&Display!AJ4&Summary!K6,INDEX(JCB!C1:CB1&JCB!C2:CB2&JCB!C3:CB3,0),0))")

  • these references need to be made as As long (for all formula)

    1. c = Evaluate("=INDEX(JCB!C6:CB500,MATCH(Display!D" & i & ",JCB!B6:B500,0),MATCH(Display!I3&Display!AJ4&Summary!K6,INDEX(JCB!C1:CB1&JCB!C2:CB2&JCB!C3:CB3,0),0))")