+ Reply to Thread
Results 1 to 4 of 4

Implement Index/match array formula in VBA

Hybrid View

  1. #1
    Registered User
    Join Date
    05-18-2020
    Location
    Richmond, Virginia
    MS-Off Ver
    MS Excel
    Posts
    3

    Implement Index/match array formula in VBA

    Hello,

    I'm trying to create a macro that gives me a vendor number from another worksheet. Below is the formula that I'm currently using. I'm using this formula for about 230 rows My file is pretty big and I'm basically trying to make it smaller by creating macros, which I'm not very good at.

    ESTDATA is a worksheet within my workbook.

    Can someone help me figure out how to make the function below work in VBA? I would greatly appreciate it. I've spent about 2 hours trying to figure it out. If you guys could help me paste the values at the end, that would be even better!

    My formula: {=(INDEX(ESTDATA!$P:$P,MATCH($A5,IF(ESTDATA!$P:$P<>"",IF(ESTDATA!$P:$P<>"No Vendor",IF(ESTDATA!$P:$P<>"BUDGT",IF(ESTDATA!$P:$P<>"00000",ESTDATA!$J:$J),IF(ESTDATA!$P:$P<>"GENER",,0)))))))}
    Last edited by 6StringJazzer; 05-19-2020 at 11:35 PM. Reason: Moved from Excel Formulas & Functions; clarified title

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,156

    Re: Implement Index/match array formula in VBA

    Why not just populate entire range with formula via code and change to values after...
    Something like...
    With Range("A1:A5")
       .formula = "=C1+D1"
       .value = .value
    End with
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Registered User
    Join Date
    05-18-2020
    Location
    Richmond, Virginia
    MS-Off Ver
    MS Excel
    Posts
    3

    Re: Implement Index/match array formula in VBA

    I get an "#N/A error after the macro runs?

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,156

    Re: Implement Index/match array formula in VBA

    Have no idea what you are referring to...

+ 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. [SOLVED] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?
    By Renejorgensen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2016, 10:54 AM
  2. [SOLVED] Index / Match - match 3 input values and return the results from the index
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2016, 07:34 PM
  3. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  4. INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges
    By Andrew-Mark in forum Excel General
    Replies: 3
    Last Post: 02-27-2015, 10:56 PM
  5. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  6. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  7. Replies: 3
    Last Post: 05-02-2013, 01:31 AM

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