+ Reply to Thread
Results 1 to 13 of 13

Index and Match Run time error 1004

  1. 03-20-2018, 06:05 AM #1
    forrestgump1980
    forrestgump1980 is offline
    Forum Contributor
    Join Date
    06-06-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    180

    Index and Match Run time error 1004

    Hi there,


    I am trying to write some code to do an index and match to return a value and run through a loop. The code I have written so far is below. But I get runtime error 1004 when I reach the line:-

    Please Login or Register  to view this content.
    any help is greatly appreciated.

    Please Login or Register  to view this content.
    Kind regards,

    Forrestgump1980
    Reply With QuoteRegister To Reply
  2. 03-20-2018, 06:25 AM #2
    xlnitwit
    xlnitwit is offline
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Index and Match Run time error 1004

    Hi,

    I'd start by removing the brackets around (prerescol).

    Is there more to the error message than 1004?
    Don
    Please remember to mark your thread 'Solved' when appropriate.
    Reply With QuoteRegister To Reply
  3. 03-20-2018, 06:30 AM #3
    forrestgump1980
    forrestgump1980 is offline
    Forum Contributor
    Join Date
    06-06-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Index and Match Run time error 1004

    Yes there is, unable to get the Match property of the WorksheetFunction class
    Reply With QuoteRegister To Reply
  4. 03-20-2018, 06:32 AM #4
    xlnitwit
    xlnitwit is offline
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Index and Match Run time error 1004

    That means the lookupval is not being found. Are you sure they all match? What data type are they?
    Reply With QuoteRegister To Reply
  5. 03-20-2018, 06:45 AM #5
    kev_
    kev_ is offline
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Index and Match Run time error 1004

    Should VBA always find a match?

    If not, wrap like this
    Please Login or Register  to view this content.
    If it should, then this will help tell you what it cannot find - the count will be zero and the value not found is in the title
    Please Login or Register  to view this content.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear
    Reply With QuoteRegister To Reply
  6. 03-20-2018, 06:52 AM #6
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Index and Match Run time error 1004

    Try this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  7. 03-20-2018, 07:02 AM #7
    xlnitwit
    xlnitwit is offline
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Index and Match Run time error 1004

    For what it's worth, it would be more efficient to use
    Please Login or Register  to view this content.
    rather than calling Index in
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 03-20-2018, 07:11 AM #8
    kev_
    kev_ is offline
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Index and Match Run time error 1004

    @xlnitwit and @Norie
    - that combo is much better than my (predictable ) On Error Resume Next which now looks rather limp by comparison
    Last edited by kev_; 03-20-2018 at 07:50 AM.
    Reply With QuoteRegister To Reply
  9. 03-20-2018, 07:19 AM #9
    xlnitwit
    xlnitwit is offline
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Index and Match Run time error 1004

    @kev_

    Thanks for the rep.
    Reply With QuoteRegister To Reply
  10. 03-20-2018, 07:47 AM #10
    forrestgump1980
    forrestgump1980 is offline
    Forum Contributor
    Join Date
    06-06-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Index and Match Run time error 1004

    Thanks for all your help everyone, very useful.
    Reply With QuoteRegister To Reply
  11. 03-20-2018, 08:32 AM #11
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Index and Match Run time error 1004

    Hi !

    No need WorksheetFunction, keep only Application.Match so no need On Error

    just using a Variant variable and test its result with IsError or IsNumeric …
    Reply With QuoteRegister To Reply
  12. 03-20-2018, 08:56 AM #12
    kev_
    kev_ is offline
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Index and Match Run time error 1004

    thank you @Mark L
    - did you read post#6?
    - that is what is happening in Norie's code
    Reply With QuoteRegister To Reply
  13. 03-20-2018, 08:58 AM #13
    xlnitwit
    xlnitwit is offline
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Index and Match Run time error 1004

    I'm not sure it's actually any better than the Worksheetfunction version. You're trading error handling for a Variant and a late bound function call, so the performance is most likely slightly worse. Really more a matter of personal preference in my opinion.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Index Array Match - 1004 Error
    By Nipius in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-07-2016, 10:54 AM
  2. VBA Index/match Error 1004
    By albertli in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2015, 09:37 PM
  3. [SOLVED] Match & Index code results in Runtime error 1004
    By DJvdW in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-27-2013, 03:52 AM
  4. [SOLVED] WorksheetFunction.match run time error '1004' with numbers but not text
    By Hussar13 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-13-2013, 05:31 PM
  5. worksheetfunction.match giving run time error '1004'
    By devo2511 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-15-2010, 04:47 PM
  6. run-time error '1004 Unable to get Match property of WorksheetFunction class
    By exl044 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-08-2010, 02:23 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1