+ Reply to Thread
Results 1 to 3 of 3

error 2029 with match named range

  1. #1
    Registered User
    Join Date
    01-20-2013
    Location
    Kampala
    MS-Off Ver
    Excel 2007
    Posts
    9

    error 2029 with match named range

    Hi all,
    I recently changed a sub routine from multiple if, elseif... to using a for loop. this sub recieved input from a user and wrote the data to a sheet column. The columns in which the data is written depends on the item "each item has "OWN" store(columns). I converted to for loop because i used consistent naming e.g AA_Qtty, BB_Qtty, CC_Qtty and i had the list of items somewhere i.e (AA, BB, CC). so i figured i could just concatenate the names of items to prefix i.e "CC"+"_Qtty" and pass this to the necessary searches
    Please Login or Register  to view this content.
    the row topRow and col as you may be able to see are used to locate the places to place the items properties.
    However the line with match rejects the strin value and returns error 2029. This code worked previously with the actual name (hard coded). I tested the code by replacing the strin value for the match with an actual name and the error did not occur. Closer inspection made me realise that unlike the other methods match works when the name is coded without the double quotes
    i.e
    Please Login or Register  to view this content.
    instead of
    Please Login or Register  to view this content.
    as i would have used in the other methods such as
    Please Login or Register  to view this content.
    It seems to me that the strin is passed with its double quotes i.e strin="value". However this seems to conflict the evaluate function by breaking it up into two strings and characters in the middle
    i.e "=Match(True," value, "<>0,0)".
    So it seems to me that the only solution is to hardcode, but i dont like writing alot of code when i can just use a less than 10 lines in place of over 80 lines of code.

    All suggestions appreciated

    Stephen

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,900

    Re: error 2029 with match named range

    I would suggest you post a sample workbook with some typical data and, of course, the code. It would be useful to see the original code together with the various attempts you have made to loop it.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


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

    Re: error 2029 with match named range

    you need
    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

+ 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