+ Reply to Thread
Results 1 to 3 of 3

Match Function in VBA Vlookup

  1. #1
    Registered User
    Join Date
    01-11-2015
    Location
    India
    MS-Off Ver
    2010
    Posts
    19

    Exclamation Match Function in VBA Vlookup

    Hi All,

    The Table I have created is as follows.

    Paint Name Colour Boxes
    Paint 1 White 5 Available
    Paint 1 Green 3 Available
    Paint 2 White Out of Stock
    Paint 2 Green 10 Available

    I have entered the preferred paint as Paint 1 in textbox1. I have two command buttons named White & Green. Now I want to check what is the input in textbox 1 and based on that the data should display when I click white or green button.

    Below is the code I have written. But the problem is its checking only the textbox1 input but not both textbox1 and colour. I tried to use match function to filter but getting an error stating "Application or Object Defined Error". Can someone please guide how to solve this or suggestions are welcome if any other method to achieve this rather than using VLookUp.

    Please Login or Register  to view this content.
    Regards,
    Dheepak

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Match Function in VBA Vlookup

    Can someone please guide how to solve this
    First step, I think, is to become familiar with VBA's debugging tools: http://www.cpearson.com/excel/DebuggingVBA.aspx Step through the function and identify which statement is giving you the error. Then you can focus on the problems with that statement.

    I suspect that the problem is in the vlookup statements, so I would suggest reviewing the help file for the VLOOKUP() function. https://support.office.com/en-us/art...__toc309306714 Note that the 2nd parameter for the VLOOKUP() function is supposed to be a range. In your statements, this argument is a MATCH() function. Looking at the help file for the MATCH() function, we learn that MATCH() returns an integer. I suspect that your error is because Excel/VBA does not know what to do with this integer argument being passed to a range parameter.

    I think much of your difficulty here is that both of your lookup values occur multiple times in your lookup arrays. I would probably rework the main lookup table so that your lookup value only occurs once in each lookup range. This is not the kind of thing I do much of, so I'm not sure exactly how I would do it. Perhaps a 2D lookup table with paint names down the left column and paint colors across the top:
    Please Login or Register  to view this content.
    With my lookup table structured this way, I could use one match function down the left column with paint # to find the row number, a second match function across the top with color to find the column number, then a simple .cells(rownum,colnum) method to return the value inside the table.

    If I was forced to leave the data in a list format like in your first post, I would probably add a helper column and use that to perform the search. This helper column would be a simple concatenate(A2,B2) copied down that would join paint name and color into a single text string (I assume that this will be unique for each row). Then concatenate paint name and color into the inc1 variable (or whatever you want to use to hold the lookup value), and search the helper column for that combination.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    01-11-2015
    Location
    India
    MS-Off Ver
    2010
    Posts
    19

    Re: Match Function in VBA Vlookup

    Thank you for the information & help MrShorty.

+ 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] Vlookup to Pivot Table, using Match function, returns error if can't find match value
    By AndrewHowarth in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-16-2015, 12:10 AM
  2. Vlookup isn't the right function, tried MATCH as well
    By NS ROXX in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-02-2014, 11:46 PM
  3. Need help nesting an index/match function within a Vlookup function.
    By Christopher135 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2013, 06:16 PM
  4. VLOOKUP or/and MATCH Function
    By itstudent13 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2013, 08:40 PM
  5. [SOLVED] Help with If, Vlookup, and or Match Function
    By jtoscani in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-30-2012, 11:15 PM
  6. Vlookup and Match function
    By RMulligan in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-28-2008, 03:00 AM
  7. Help with vlookup/match function
    By -emma- in forum Excel General
    Replies: 2
    Last Post: 01-28-2007, 07:23 PM
  8. Vlookup & match function
    By QQ29 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-28-2006, 05:25 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1