Array is locked error

niall91

New Member
Joined
Jul 21, 2020
Messages
35
Office Version
  1. 2019
Platform
  1. Windows
Hi guys

Im getting an error message when i try to ReDim Preserve my array to take out all the empty positions.
the error is 'This array is fixed or temporarily locked'. any ideas?


VBA Code:
Private Sub ComboBox1_Change()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sales Chart")
Dim i As Integer, r As Integer, n As Integer, lr As Integer
Dim additeam As Boolean
Dim myarray() As Variant, iteam As Variant
n = Application.WorksheetFunction.Match(Me.ComboBox1.Value, sh.Range("B:B"), 0)

Me.ComboBox2.Clear
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
r = 0
ReDim myarray(0 To lr)

For i = n To lr
    If sh.Cells(i, 2) = Me.ComboBox1.Value Then
        If myarray(r) = "" Then
              myarray(r) = sh.Cells(i, 4).Value
                        Else
                        For Each iteam In myarray()
                            If sh.Cells(i, 4) = iteam Then
                                additeam = False
                                GoTo nexti
                                    Else
                                        additeam = True
                            End If
                        Next iteam
                                If additeam = True Then
                                    r = r + 1
                                    myarray(r) = sh.Cells(i, 4).Value
                                End If
        End If
    End If
    
nexti:
Next i

ReDim Preserve myarray(0 To r)

For i = LBound(myarray) To UBound(myarray)
    
    Me.ComboBox2.AddItem myarray(i)
    
Next i

End Sub
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Not sure why you are getting an error, but does this do what you want
VBA Code:
Private Sub ComboBox1_Click()
   Dim Dic As Object
   Dim Cl As Range
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sales Chart")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Cl.Value = Me.ComboBox1.Value Then
            Dic(Cl.Offset(, 2).Value) = Empty
         End If
      Next Cl
   End With
   Me.ComboBox2.list = Dic.Keys
End Sub
 

niall91

New Member
Joined
Jul 21, 2020
Messages
35
Office Version
  1. 2019
Platform
  1. Windows
Not sure why you are getting an error, but does this do what you want
VBA Code:
Private Sub ComboBox1_Click()
   Dim Dic As Object
   Dim Cl As Range
  
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sales Chart")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Cl.Value = Me.ComboBox1.Value Then
            Dic(Cl.Offset(, 2).Value) = Empty
         End If
      Next Cl
   End With
   Me.ComboBox2.list = Dic.Keys
End Sub
This works really well thanks a million (y)(y)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,126
Messages
5,628,859
Members
416,345
Latest member
sayad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top