+ Reply to Thread
Results 1 to 10 of 10

Adding a variable to index & match function using VBA

  1. #1
    Registered User
    Join Date
    01-21-2012
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    16

    Adding a variable to index & match function using VBA

    I have tried to add a variable to an index & match worksheet function in vba without success as shown below.

    The variable is mat_ox

    Please Login or Register  to view this content.

    Any assistance would be greatly appreciated.

    Thank you

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

    Re: Adding a variable to index & match function using VBA

    Have you tried:

    Please Login or Register  to view this content.
    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
    Registered User
    Join Date
    01-21-2012
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Adding a variable to index & match function using VBA

    Thank you for your assistance

    I have now just tried your recommedation and it did not work. However, vba return "invaild character" error.

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

    Re: Adding a variable to index & match function using VBA

    Can I see the entire code and maybe a sample worksheet?

  5. #5
    Registered User
    Join Date
    01-21-2012
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Adding a variable to index & match function using VBA

    Thank you for your prompt reply.
    Here is a copy of the code.

    It is in the early stages of development!
    Essentialy I want to validate data in a worksheet against reference data (defined upper and lower limits) in another spreadsheet.

    For example a record with a "mat_ox" value of "blabla" would have defined upper and lower limits for each measure eg copper (Cu) Uranium (U) and Specific Gravity (SG). The macro tests each value aganst the data in Ref_Table spreadsheet and if the value is outside the upper and lower limits the cell is flagged (infilled red).


    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-21-2012
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Adding a variable to index & match function using VBA

    here is the ws
    Attached Files Attached Files

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

    Re: Adding a variable to index & match function using VBA

    Hi prestopr,

    I've altered your code but am unable to test the coloring aspect since I'm using 2003 and it protests against the "too many formats" in your sample - let me know if it works for you:

    Please Login or Register  to view this content.
    Last edited by xladept; 02-07-2013 at 04:02 PM.

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Adding a variable to index & match function using VBA

    mat_ox is a string so you must enclose the value in quotes
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

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

    Re: Adding a variable to index & match function using VBA

    Hi prestopr,

    It works with Joseph P's code up to where the coloring starts if you do it this way:

    Please Login or Register  to view this content.

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Adding a variable to index & match function using VBA

    yeah-there's no match for mat_ox in that table :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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