+ Reply to Thread
Results 1 to 20 of 20

VBA index match with multiple occurrences

  1. #1
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    VBA index match with multiple occurrences

    Hi guys, I'm racking my brain here. I'm trying to find a vba code that will allow index and match for multiple occurrences, then having that data filled into a column so I can pull that data into a userform. Basically each time an employee's name (will always be cell L1) comes up in col B, I need the date from same row, col E. Then have that date pasted into col M, starting with cell M1 and continuing down the rows for all occurrences, stopping after they all have been populated so I don't have 50 cells with formulas and only 3 occurrences of the employee's name. To make matters worse, I will also need the row number from each occurrences to be pasted into col N.

    From there, I have the vba to load it into my userform for the next fun part of my project.

    THANK YOU!!!

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: VBA index match with multiple occurrences

    Is this what you want? Run this on the worksheet.
    Please Login or Register  to view this content.
    多么想要告诉你 我好喜欢你

  3. #3
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: VBA index match with multiple occurrences

    AAAHHHHHH!!!!! you make it look too easy. thank you for the quick and very helpful reply.

    I just ask now if it is possible to tweak it a little so that it pastes the data M1, M2, M3 and so on instead of the same row that the info is found it (M4,M5,M10, etc). Also, I found that it is case sensitive, any way of making it disregard the case? UCase something or another isn't it? And one last bit, it errors/debugs if the name entered is not found.

    Thank you once again as I bow down to the excel all mighty.

  4. #4
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: VBA index match with multiple occurrences

    Sorry, I missed the start at M1 part. Try this.
    Please Login or Register  to view this content.
    Last edited by millz; 11-25-2014 at 10:22 PM. Reason: changes to code

  5. #5
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: VBA index match with multiple occurrences

    Nice, exactly what I needed! Still working on the case sensitivity and the errors when the name is not found.

  6. #6
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: VBA index match with multiple occurrences

    I just made changes to the code in previous post, forgot UCase on the If statement.

    As for the name not found, ideally it should not be giving any errors. What was highlighted when you debug?

  7. #7
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: VBA index match with multiple occurrences

    Hmmmmmmm. now it's not giving me the error. I wonder if it was just a fluke or I hit something.

  8. #8
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: VBA index match with multiple occurrences

    The case sensitivity is still giving me issues. If I type Last, it doesn't populate the info for last.

  9. #9
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: VBA index match with multiple occurrences

    It's working properly on my side. Make sure the cells don't contain any additional spaces.

  10. #10
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: VBA index match with multiple occurrences

    I found the issue. It's stopping once it reaches the last consecutive row. B3=Last B4=Test, B5=Test, B6=test, B7=last, B8=Test. When I run a query for "Last", I only get B3, not B7. When I run a query for "Test", I only get B4:B6, not B8.

    I guess that the search needs to run the entire column and not stop until the very last row?

  11. #11
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: VBA index match with multiple occurrences

    I see. I thought your data was sorted by the name. Try this

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: VBA index match with multiple occurrences



    YES!! Thank you very, very much.

  13. #13
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: VBA index match with multiple occurrences

    I was looking at loop, but couldn't figure out where to add it.

  14. #14
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: VBA index match with multiple occurrences

    One problem still is that if there isn't a name listed or only one occurance, I get a debugging issue "Run-time erro'381': Could not set the List property. Invalid property array index. I have a userform making the lists from the code above, then it is supposed to open a new userform with the info populated from the first results. If I disable the call for new userform, it works regardless of number of occurances, but I'll still need the second userform to show. Any thoughts?

    Code:
    Please Login or Register  to view this content.

  15. #15
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: VBA index match with multiple occurrences

    This code should not be producing any errors if I assume correctly.

    Show the code of the new userform, specifically where you start loading the list box?

  16. #16
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: VBA index match with multiple occurrences

    New form based on the dates from the first form.
    Please Login or Register  to view this content.

  17. #17
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: VBA index match with multiple occurrences

    How about this?
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: VBA index match with multiple occurrences

    that fixes the bug, but what if they don't have anything listed? It just starts the second userform with a blank dropdown

  19. #19
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: VBA index match with multiple occurrences

    also, if there is no data to choose, and they click submit, it goes crazy

  20. #20
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: VBA index match with multiple occurrences

    Quote Originally Posted by jwlamb View Post
    but what if they don't have anything listed?
    You have to ask yourself what you want to do.

    List out all possible scenarios, no data, 1 data, or multiple.
    Define a way how each scenario should appear, and make code handle it.
    You know it goes crazy if there's no data, so make the code check if there's any data before proceeding.
    It is called validation.

    I can't simply fix everything for you when I don't even know your process or how you want the code to handle.

+ 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. Replies: 5
    Last Post: 10-16-2016, 02:33 AM
  2. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  3. Issue using Index/Match to pull multiple occurrences of "Match" criteria
    By aridfriedman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2014, 11:38 AM
  4. Replies: 10
    Last Post: 12-18-2012, 07:59 AM
  5. Replies: 13
    Last Post: 12-13-2012, 11:44 AM

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