+ Reply to Thread
Results 1 to 8 of 8

VBA Vlookup issues

  1. #1
    Registered User
    Join Date
    05-13-2017
    Location
    Bozeman, Montana
    MS-Off Ver
    2016
    Posts
    14

    VBA Vlookup issues

    Hey, I have been working on a project for awhile now that has involved a large inventory sheet to keep track of medical supplies. Currently I have been working on a two way Vlookup. I can look up an items serial sumber and it will auto fill the name or I can look up the name and it will auto fill the serial number. With my two weeks of learning of VBA in my spare time I have built a couple of different small Macros that function as Vlookup. However there are 3 problems I am running into, which is why I'm here.
    1) The reference sheet or master sheet, where I am looking up the data, is a separate workbook. I can get around this with a Vlookup Formula in the cell itself, but in VBA I keep hitting brick walls, and it wont pull from the external reference book.
    2) I can get Vlookup to work for a single row, but in my workbook I have 50 rows across 23 worksheet. I could copy and paste the line of code 50 times and adjust a couple of data points, but there has to a be a more efficient way for the program to do it. I don't mind putting in the workm but when I hit execute I don't want to strain the system.
    3)Goes hand in hand with (2) I have 23 worksheets I want to apply this to, however, I want to exclude the 24th worksheet as it is a totals worksheet. How do I apply this to some worksheet and not others in the workbook. I have tried building an array, but the Vlookup function keeps rejecting it.
    Thanks for any help or advise you can give.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA Vlookup issues

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: VBA Vlookup issues

    For part 1), the easiest way to do something like this would be to open the workbook, pull any data from it, and then close the workbook. You can do this while the screen does not update making it almost unnoticeable, using something like this.
    Please Login or Register  to view this content.
    For part 2), this would be best accomplished using a loop. If you are using a cell in a given row as your lookup value, you could do something like this, where "i" is the row number
    Please Login or Register  to view this content.
    For part 3), this could again be done with a loop, using a For...Each loop like so:
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    05-13-2017
    Location
    Bozeman, Montana
    MS-Off Ver
    2016
    Posts
    14

    Re: VBA Vlookup issues

    Hey, sorry it took a little to respond. I have attached a copy of the workbook that I am trying to create a VBA for (labeled MES 2 Test), and also
    A copy of the workbook with the source data (labeled Masterlist). Basically there are 23 sheets in the MES 2 workbook, each being an individual supply
    box, which are labeled box 1-20, as well as Trauma Bag, Medical Bag, and Individual Equipment. The last sheet in the workbook, is a totals sheet, with
    that condenses and totals everything in the previous worksheets for resupply and reordering. Every worksheet row is for a single item. Each item has
    an NSN (which is the skew, or serial number), a component number (which is irrelevant for the issue at hand), and a Item Description or name.
    What I am trying to do is be able to look up an item either by name or NSN, and have the spreadsheet to auto populate, Vlookup, the missing cell.
    For example, emergency bandages have a dozen different names, but they all have an NSN. I would punch the NSN into the appropriate cell and the program would
    fill in the right name. Or the opposite, I have a IV bag and I put in the Item Description and the spreadsheet would auto fill the NSN. The only sheet excluded
    from this operation is the Totals sheet at the end. It already has a couple of Macros to find, sort, and total the other sheets.
    Hopefully this makes sense. I removed my attempts to create the Vlookup Macro, more to save face than anything.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-13-2017
    Location
    Bozeman, Montana
    MS-Off Ver
    2016
    Posts
    14

    Re: VBA Vlookup issues

    Thanks for the leads, I will print these out and disect them to figure out whats going on and then see if I can apply them how I need. It looks pretty straight forward and actually smaller than I expected.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA Vlookup issues

    Here's some code for your MES kit ThisWorkbook Module and your Mes kit book:

    Please Login or Register  to view this content.
    *BTW - I edited the existing code
    Attached Files Attached Files
    Last edited by xladept; 08-29-2018 at 10:13 PM.

  7. #7
    Registered User
    Join Date
    05-13-2017
    Location
    Bozeman, Montana
    MS-Off Ver
    2016
    Posts
    14

    Re: VBA Vlookup issues

    Thanks so much! I tested it out and it works great. I have now locked it away a master copy so that the only person at risk of messing it up every is me.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA Vlookup issues

    You're welcome and thanks for the rep!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

    BTW - You write pretty good code

+ 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. VLookup or IF issues
    By Dragoniak in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-02-2016, 05:04 PM
  2. [SOLVED] issues with vlookup
    By grkchakri in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-08-2014, 02:31 AM
  3. [SOLVED] VLOOKUP Issues
    By tapsmiled in forum Excel Formulas & Functions
    Replies: 33
    Last Post: 12-14-2013, 11:01 PM
  4. vLOOKUP Issues
    By dsciola in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-24-2013, 02:35 PM
  5. [SOLVED] Vlookup Issues
    By Sky188 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-10-2013, 11:51 AM
  6. Vlookup issues
    By rhuark in forum Excel General
    Replies: 11
    Last Post: 06-30-2011, 10:50 AM
  7. VLOOKUP issues
    By mojobaabby in forum Excel General
    Replies: 1
    Last Post: 09-23-2010, 08:37 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