+ Reply to Thread
Results 1 to 8 of 8

Dynamic US Map was working fine, but now I'm getting WorksheetFunction.Match 1004 errors

  1. 01-22-2014, 07:06 PM #1
    cdutton
    cdutton is offline
    Registered User
    Join Date
    01-22-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Dynamic US Map was working fine, but now I'm getting WorksheetFunction.Match 1004 errors

    Hello, new to the forum (and to VBA in general).

    I'm working on building a tool in Excel that dynamically changes the fill color of each state in a US map, based on values in another sheet. It basically works by finding each state name and value, returning the fill color in the cell one column to the right, and applying that fill color to the appropriate state in the map.

    It has been working off and on, but recently broke for no apparent reason. I now receive a 1004 Error (Unable to get the Match property of the WorksheetFunction class).

    I have one piece of code (functioning properly) that changes the fill color depending on the value of the cell (which in this case is a percentile):

    Please Login or Register  to view this content.
    The second piece of code, which actually populates the colors in the map, looks like this:

    Please Login or Register  to view this content.
    Where STATES_VISITS, and STATE_COLORS_VISITS are named ranges in Excel. I'm getting the error in the following line:

    Please Login or Register  to view this content.
    I've also attached the full excel file in case that helps. HUGE thank you to anyone who can help shed some light on this!
    -Chris
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-22-2014, 07:20 PM #2
    TMS
    TMS is offline
    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,461

    Re: Dynamic US Map was working fine, but now I'm getting WorksheetFunction.Match 1004 erro

    Works for me. Well, it doesn't crash. I don't know if anything changes. I click on the button, it flickers, it comes back.

    I can put a break on the module and step through the code and it goes through so it IS doing something and it's not breaking.

    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

    Reply With QuoteRegister To Reply
  3. 01-22-2014, 07:40 PM #3
    cdutton
    cdutton is offline
    Registered User
    Join Date
    01-22-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Dynamic US Map was working fine, but now I'm getting WorksheetFunction.Match 1004 erro

    Hmmm, did you try dropping in a new set of numbers in column B, running the ChangeColor script, then try updating the map?
    Reply With QuoteRegister To Reply
  4. 01-22-2014, 08:18 PM #4
    TMS
    TMS is offline
    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,461

    Re: Dynamic US Map was working fine, but now I'm getting WorksheetFunction.Match 1004 erro

    Hmmm, did you try dropping in a new set of numbers in column B, running the ChangeColor script, then try updating the map?
    How would I know I should do that? What is the process you go through? How do you reproduce the fault? What is a valid set of numbers? Is there an invalid set of numbers? Am I supposed to make it up? Do I run the macros in the VBE?
    Reply With QuoteRegister To Reply
  5. 01-22-2014, 08:33 PM #5
    cdutton
    cdutton is offline
    Registered User
    Join Date
    01-22-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Dynamic US Map was working fine, but now I'm getting WorksheetFunction.Match 1004 erro

    Strange, I'm still getting that 1004 error every time I try updating the map...

    Ideally I'd like to be able to drop in new sets of values into column B, but it looks like that's what's causing the error. As a test, would you mind trying some random values in Column B (i.e. Randbetween (1,1000)), running the ChangeColor script so that column C updates, and then seeing if the map will update accordingly on your end?

    Really appreciate the help!
    Reply With QuoteRegister To Reply
  6. 01-23-2014, 10:09 AM #6
    TMS
    TMS is offline
    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,461

    Re: Dynamic US Map was working fine, but now I'm getting WorksheetFunction.Match 1004 erro

    OK, done that, got the crash.

    Please Login or Register  to view this content.
    intState = 22
    strStateName = MICHIGAN
    intStateValue = 55

    ?rngstates.Address
    $A$2:$B$51

    rngStates.Rows.Count = 50


    I'm guessing the problem is here:

    Please Login or Register  to view this content.

    There's nothing in column E so that range becomes E1:E2 as Lastrow will be 1.

    Have you tidies up the worksheet and not changed the code to match?


    Regards, TMS
    Reply With QuoteRegister To Reply
  7. 01-23-2014, 10:35 AM #7
    cdutton
    cdutton is offline
    Registered User
    Join Date
    01-22-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Dynamic US Map was working fine, but now I'm getting WorksheetFunction.Match 1004 erro

    Update: SOLVED

    Turns out that if you use an approximate match in the MATCH function ("true"/"1"), the reference array needs to be in ascending/descending order. I was able to update my code to exact match and it works perfectly:

    Please Login or Register  to view this content.
    Thanks again for looking into this!
    -Chris
    Reply With QuoteRegister To Reply
  8. 01-23-2014, 10:42 AM #8
    TMS
    TMS is offline
    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,461

    Re: Dynamic US Map was working fine, but now I'm getting WorksheetFunction.Match 1004 erro

    You're welcome.

    Still a bit concerned about the reference to a column with no data for the loop ... not sure that's doing what you think it is.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    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 MATCH MATCH working fine and then NOT WORKING for a random row or two.
    By HeikEve in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-24-2013, 09:32 AM
  2. [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
  3. Run-time error 1004: Unable to get the Match property of the WorksheetFunction class
    By maffmommie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-13-2010, 09:32 AM
  4. 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
  5. 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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1