Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: "This array is fixed or temporarily locked" error in VBA

  1. #1
    Board Regular
    Join Date
    Dec 2007
    Location
    Oakhurst, New Jersey, USA
    Posts
    459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question "This array is fixed or temporarily locked" error in VBA

    Dear All,

    What is the solution for the error-

    Code:
     
    Run-time error '10':
     
    This array is fixed or temporarily locked
    I have code that checks if necessary data is on the active worksheet. If not, a message is displayed that gives the user the option to exit the macro or move to the next sheet in the file:

    Code:
        For Each Word In Array("CUSTOMER:", "PO No:", "B/L:")
            Set aRange = Range("a1:iv100").Find(What:=Word, _
                  LookAt:=xlWhole, MatchCase:=False)
            If aRange Is Nothing Then
                msg = "All required fields are not present" & vbCrLf & vbCrLf
                msg = msg & "on the sheet: " & ActiveSheet.NAME & vbCrLf & vbCrLf
                msg = msg & Word & " is missing." & vbCrLf & vbCrLf
                msg = msg & "Continue macro on next sheet?"
                If MsgBox(msg, 4, "  Attention:") = vbYes Then
                    GoTo DONE:
                Else
                    GoTo DONE2:
                End If
            End If
        Next Word
    I get the error after the test fails and the user selects to continue the macro after moving to the next sheet.

    It seems when this code is run on the next sheet, the Array is still "locked" from the prior loop.

    Is there a way to reset the Array so it is not locked?

    Best regards,

    GL

  2. #2
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: "This array is fixed or temporarily locked" error in VBA

    What line does it actually bug out on? Have you provided this line? Can you include the code for the labels Done1 and Done2?

  3. #3
    Board Regular
    Join Date
    Dec 2007
    Location
    Oakhurst, New Jersey, USA
    Posts
    459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "This array is fixed or temporarily locked" error in VBA

    What line does it actually bug out on?
    The macro crashes at the line
    For Each Word In Array("CUSTOMER:", "PO No:", "B/L:")


    Can you include the code for the labels Done1 and Done2?
    DONE exits to another part of the macro.
    DONE2 exits to the end of the macro.

  4. #4
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: "This array is fixed or temporarily locked" error in VBA

    And how have you dimensioned variable word?

  5. #5
    Board Regular
    Join Date
    Dec 2007
    Location
    Oakhurst, New Jersey, USA
    Posts
    459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "This array is fixed or temporarily locked" error in VBA

    Yes I have like this:
    Code:
        Dim Word As Variant

  6. #6
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: "This array is fixed or temporarily locked" error in VBA

    That code doesn't error for me. Have you tried shutting down Excel (maybe rebooting too) and seeing if it still errors?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •