+ Reply to Thread
Results 1 to 3 of 3

Convert INDEX MATCH AGGREGATE FORMULA to VBA code

  1. #1
    Registered User
    Join Date
    07-04-2019
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    2

    Convert INDEX MATCH AGGREGATE FORMULA to VBA code

    I have this formula that works but I can seem to convert it to a VBA code. When I run the code, there is no error, but no value appears. Appreciate your help.

    I am basically trying to extract a value from another workbook. There is a master field selection in the activesheet that requires me to Portfolio in B2 and Field in D3. It then runs an index match based on the portfolio and field in another workbook (Balanced Portfolio AA Summary Table). The list of portfolios are from D5:CR5 while the list of fields are from B78:B298. The fields have duplicated values and hence I need to extract the correct one but altering the column range using large.

    My formula:
    INDEX('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:CR298,AGGREGATE(14,6,(ROW('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:D298)-ROW('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78)+1)/(('[Balanced Portfolio AA_280219.xlsx]Summary Table'!B78:B298 =D3)*(INDEX('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:CR298,0,MATCH(B2,'[Balanced Portfolio AA_280219.xlsx]Summary Table'!D5:CR5,0))<>"")),1),MATCH(B2,'[Balanced Portfolio AA_280219.xlsx]Summary Table'!D5:CR5,0))


    My code:
    Please Login or Register  to view this content.

    I defined sh1 as '[Balanced Portfolio AA_280219.xlsx]Summary Table' from another workbook
    and comboSh1 as the sheet in the active workbook.

    I am trying to find obtain a value that has duplicated variables and hence I need this function.

    I think there is something wrong when i tried to convert

    AGGREGATE(14,6,(ROW('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:D298)-ROW('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78)+1)/(('[Balanced Portfolio AA_280219.xlsx]Summary Table'!B78:B298 =D3)

    into

    Please Login or Register  to view this content.
    not sure how one range can be = to another range.
    Last edited by Pepe Le Mokko; 07-05-2019 at 03:07 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Convert INDEX MATCH AGGREGATE FORMULA to VBA code

    Code Tags Added
    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

  3. #3
    Registered User
    Join Date
    07-04-2019
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    2

    Re: Convert INDEX MATCH AGGREGATE FORMULA to VBA code

    Hi. Apologies I missed that out. Thanks for your help. I will take note in future

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Dynamic Sort via Index/Match/Aggregate
    By datbigdog in forum Excel General
    Replies: 9
    Last Post: 06-15-2019, 09:02 PM
  2. [SOLVED] Match Multiple Criteria INDEX & Aggregate
    By remyte in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-26-2019, 09:15 AM
  3. [SOLVED] Create List - Index Match or Index Aggregate or other?
    By bambamclint in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-10-2019, 01:30 PM
  4. Replies: 2
    Last Post: 12-05-2018, 07:59 AM
  5. [SOLVED] Index/Aggregate/row match with conditions, pivot summary replacement
    By Shruder in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2018, 08:36 AM
  6. How to convert Matrix (Index/Match) into VBA Code?
    By PRMiranda in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-03-2017, 07:38 AM
  7. Convert INDEX, AGGREGATE to Macro
    By VirenS in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-25-2016, 02:08 PM

Tags for this Thread

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