+ Reply to Thread
Results 1 to 2 of 2

Error 10 - Locked Array while iterating through dictionaries

  1. #1
    Registered User
    Join Date
    05-12-2016
    Location
    NZ
    MS-Off Ver
    Office 14
    Posts
    41

    Error 10 - Locked Array while iterating through dictionaries

    Hi there,

    I've got a macro which iterates through a list removing unnecessary rows until all the required criteria are met. In order to deal with the criteria, I've created two dictionaries (dict and dict2) which each have the same Keys, but different values (desired value and current value). The code snipped below finds a random row, tests to see whether the desired value has been reached against each of the keys in the dictionary. If it hasn't been reached by any of the keys, the row is deleted, dict2 value is updated to the new current value, and the loop continues. If it has been reached for any of the keys, I've used GoTo to jump past the amendment to current value and line deletion and the loop continues until all criteria are met.

    I've tested this a number of times and the loop runs successfully - updating dict2, deleting only the rows it needs to and continuing the loop. It hits an issue, however, when the value for a Key in both dict and dict2 are the same (which is meant to be the goal). This is when the second If loop allows it to bypass the deletion with GoTo, but instead I'm getting an error: "Run-time error '10': This array is fixed or temporarily locked". Does anyone know why this might be happening - it only occurs when the Key value is the same in both dictionaries. What am I missing here?

    Please Login or Register  to view this content.

    Thanks in advance for any help you can give me on this.
    -Pang

  2. #2
    Registered User
    Join Date
    05-12-2016
    Location
    NZ
    MS-Off Ver
    Office 14
    Posts
    41

    Re: Error 10 - Locked Array while iterating through dictionaries

    Resolved - turns out that when you do a For Each loop, your set (in my case this dictionary) is turned into a locked array stored as Variant. The only way to unlock the array is to end the loop formally - my GoTo meant that the loop was still active and the array was locked from last time. When the loop returned to run the next row through my For Each, it broke. I've resolved this by breaking formally, although this meant I've had to throw in a clunky bool test. Code below if anyone is interested:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Dictionaries vs Collections: Please help me understand when Dictionaries are better
    By joe31623 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-14-2016, 12:36 PM
  2. Creating Dictionary of Dictionaries
    By Dodo123 in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 03-02-2014, 11:30 AM
  3. DIMming / setting multiple ranges by iterating over an array of string elements
    By brownfox in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-12-2013, 08:14 PM
  4. Iterating one array by another
    By Poolstick in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-28-2013, 02:41 PM
  5. Macro code to continue iterating if error.
    By sid9221 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-23-2013, 03:39 PM
  6. Multiple Dictionaries Or One Dictionary
    By goss in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-15-2012, 07:58 AM
  7. Dictionaries for grouping
    By Piotr in forum Excel General
    Replies: 2
    Last Post: 05-23-2005, 07:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1