Index match multiple criteria & autofill until last row with Column as ref

  • Hi Carim,

    Would you please help me to run this macro with VBA code which is provided in the report attached to the facility index sheet. I have indicate the location in the attached file where I want to run this code.



  • Hello and Welcome to the Forum :)


    Thanks for creating a New Thread ... !!!


    Will take a look at your files and at your macros ...;)

    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:)

  • OK ...


    I do not know if you are the author of this macro ...


    But ... this is almost as bad as " Spaghetti Coding " ...:!::!::!:


    Before trying to write an Event macro ... why don't you first create a CLEAN STANDARD macro ... !!!


    Then ...You will have plenty of time... to think about how your nice macro can be launched ...


    Walking BEFORE running is often a good piece of advice ...:)

    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:)

  • At least 10 minutes ... ONLY TO CLEAN ... !!!



    QUESTION :


    Can you explain as clearly as you can ... in plain English what you are trying to achieve ....

    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:)

  • Yeah you are right ,I am very new to coding, collect all these things from many sites and try to complete my project.


    I f Display sheet B2, C2, D2 Cells value and Column2 Dates match in retrieval sheet Column 2,3 , 4 and Column 19. then extract data from Retrieval sheet Column 18 & 14 and paste to Display sheet Column 17 & 18.

  • Attached is your test file ...


    Column 17 in your sheet Display ... shows there is NOT a SINGLE match .... in the sheet Retrieval ... :S


    By the way ...NEVER use Merged Cells ...:evil::evil::evil: they only created problems ...


    Hope another contributor will be in a better position than me to help you out ...

    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:)

  • Sorry to say,my problem is still unresolved because of my misinterpretation. To solve my problem I created a new sheet that works fine with the push of a button, using your old macro code. Now I need a macro to run by clicking on the cell instead of the button. I have tried to run code by clicking on the cell but it is showing errors. Once this macro runs by clicking on cell then I will delete the button and run it by just clicking on the cell.This code will help me run my original sheet.


    I need your help to run the macro using code below instead of button.

    Code
    1. Option Explicit
    2. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    3. If Selection.Count = 1 Then
    4. If Not Intersect(Target, Sheets(RetrievalReport).Range("D4")) Is Nothing Then
    5. Call Results
    6. End If
    7. End If
    8. End Sub
  • Glad to see you have decided to take a new approach ... and start from the beginning ...


    Will take a look at your file ...

    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:)

  • Could you explain your working process and describe when exactly you want your Results macro to run


    In you sheet RetrievalReport ... What are exactly your actions ... and which actions should launch your macro ...???


    Edit:

    My guess would be .... you input in sheet Display the three cells : B1, C1 , D1 ... and once you have selected the last country ...

    ... then, the macro Results should run ... is that right ... or not ???

    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:)

    Edited once, last by Carim: ... a guess ... ().

  • Display Sheet B1, C1 and D1 are three criteria's which I want to match in Retrieval Sheets. One more fourth criteria I want to match is the cell from Display sheet D6 to D25. If all Four criteria matches in Retrieval Report sheet than macro will extract offset data from specific column of Retrieval report and will paste in to the display sheet AE to AG column.


    But the Paste cell in Display Sheet AE to AG column will be the offset to the fourth matched data from D6 to D25.


    I am now struggling to run this macro by clicking on the RetrievalReport D4 cell instead of the button .