This array is fixed or temporarily locked

Question

I am using split function and assigning the value in a variable and running the code in loop after few iterations its giving an error of "This array is fixed or temporarily locked (Visual Basic)"..

e.g; here value of movies_cat1 read from excel is in form of this------ "Movies->List All Movies , Movies->World Cinema->Asia , Movies->Movies by Language->Sinhalese , Movies->Drama"

For crow = 1 To 100

    Value = Worksheets("Movies_categories").Range("A" & crow).Value
    cat_final = Worksheets("Movies_categories").Range("B" & crow).Value

    If Value = "y" Or Value = "Y" Then

      'Loop for reading the data from tabsheet- Movies

      For crowss = 5 To 3000
        movies_cat1 = Worksheets("Movies").Range("B" & crowss).Value
        movies_language = Worksheets("Movies").Range("C" & crowss).Value

        If movies_language = "English" Then

          Temp = Split(movies_cat, ",")  'run time Error:10  occurs here..

          For Each boken_c In Temp
            flag = 0
            boken_c = Trim(boken_c)

            If RTrim(LTrim(boken_c)) = LTrim(RTrim(cat_final)) Then
              flag = 1
              GoTo Line4:
            End If
          Next boken_c
        End If
      Next crowss
    End If
Line4:    Next crow

Error occurs at this statement: Temp = Split(movies_cat, ","), it says that the array is fixed or temporarily locked, because i think initially its taking 'temp' as a variable, but while returning the value of split function, variable 'Temp' becomes array after completion of first loop(i.e after crow = 6,7....)

Solution

Your line4 label is outside the for loop on the temp variable so when you goto it leaves it locked.

You really should restructure your code to not use a goto inside the for each loop.

Maybe:

For crow = 1 To 100 

  Value = Worksheets("Movies_categories").Range("A" & crow).Value 
  cat_final = Worksheets("Movies_categories").Range("B" & crow).Value 

  If Value = "y" Or Value = "Y" Then 

    'Loop for reading the data from tabsheet- Movies 

    For crowss = 5 To 3000 
      movies_cat1 = Worksheets("Movies").Range("B" & crowss).Value 
      movies_language = Worksheets("Movies").Range("C" & crowss).Value 

      If movies_language = "English" Then 

        Temp = Split(movies_cat, ",")  'run time Error:10  occurs here.. 

        For Each boken_c In Temp 
          flag = 0 
          boken_c = Trim(boken_c) 

          If RTrim(LTrim(boken_c)) = LTrim(RTrim(cat_final)) Then 
            flag = 1 
            **Exit For**
          End If
          **If flag = 1 Then Exit For**
        Next boken_c 
      End If 
      **If flag = 1 Then Exit For**
    Next crowss 
  End If 
Next crow 

(Note the **d lines.)

Comments

[Fionnuala]

@user930679 It looks to me like you need an Exit For rather than a GoTo - these are generally not a great idea.