+ Reply to Thread
Results 1 to 14 of 14

How to convert Index(Match into VBA

  1. #1
    Registered User
    Join Date
    07-06-2010
    Location
    Sorel-Tracy, QC, Canada
    MS-Off Ver
    Excel 2016
    Posts
    29

    Thumbs up How to convert Index(Match into VBA

    Hi,

    Is anyone able to convert the following into VBA code?

    Please Login or Register  to view this content.
    I'm lost!

    Regards!
    Last edited by Alternsti; 07-14-2010 at 07:43 AM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: How to convert Index(Match into VBA

    Hello Alternsti,

    I hope I have all the parentheses correct in this. Here is the VBA version...
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    07-06-2010
    Location
    Sorel-Tracy, QC, Canada
    MS-Off Ver
    Excel 2016
    Posts
    29

    Re: How to convert Index(Match into VBA

    Hello Leith Ross,

    Thanks for your time. I've done a macro with your code but I'm always having "Run-Time error '9': Subscript out of of range". Can you look at my code and give me some hints?

    Please Login or Register  to view this content.
    Best Regards!

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to convert Index(Match into VBA

    You didn't actually open the workbook specified in the GetOpenFilename call...
    Remember what the dormouse said
    Feed your head

  5. #5
    Registered User
    Join Date
    07-06-2010
    Location
    Sorel-Tracy, QC, Canada
    MS-Off Ver
    Excel 2016
    Posts
    29

    Re: How to convert Index(Match into VBA

    Hello romperstomper,

    Thanks for your time too. I'va resolved the error code now but I got nothing as result (shall be a string value from X (see below):

    Please Login or Register  to view this content.
    Best Regards!

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to convert Index(Match into VBA

    If you got no error then a match was found, but it appears one of your match ranges is incorrect:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-06-2010
    Location
    Sorel-Tracy, QC, Canada
    MS-Off Ver
    Excel 2016
    Posts
    29

    Re: How to convert Index(Match into VBA

    Still no string coming from X.

    Please Login or Register  to view this content.

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to convert Index(Match into VBA

    Seems like a data issue then. If you are not getting a run time error, then the code is finding a match and returning a value.

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to convert Index(Match into VBA

    is this right?
    Match(W12 ?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  10. #10
    Registered User
    Join Date
    07-06-2010
    Location
    Sorel-Tracy, QC, Canada
    MS-Off Ver
    Excel 2016
    Posts
    29

    Re: How to convert Index(Match into VBA

    romperstomper, thanks for your patience. I did a test using Workbook_Activate and it's working (see below)

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    07-06-2010
    Location
    Sorel-Tracy, QC, Canada
    MS-Off Ver
    Excel 2016
    Posts
    29

    Re: How to convert Index(Match into VBA

    Quote Originally Posted by martindwilson View Post
    is this right?
    Match(W12 ?
    My goal is to use an external workbook as a database. Using Index(Match(Match into it based on the W12 cell of the activeworkbook.

  12. #12
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to convert Index(Match into VBA

    Good spot - I assumed W12 was a variable.
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    07-06-2010
    Location
    Sorel-Tracy, QC, Canada
    MS-Off Ver
    Excel 2016
    Posts
    29

    Re: How to convert Index(Match into VBA

    Thanks to all...It's working. Final result is below.

    Please Login or Register  to view this content.
    Best Regards!

  14. #14
    Registered User
    Join Date
    07-06-2010
    Location
    Sorel-Tracy, QC, Canada
    MS-Off Ver
    Excel 2016
    Posts
    29

    Re: How to convert Index(Match into VBA

    See you next time!

    [SOLVED]

+ 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