Failure to assign value to variable of the correct type, only sometimes, possibly related to UNIQUE?

[gravanoc]

I created about 130 checkboxes with a script that names them according to the cell they're in. In another script that I use to determine their values and who "owns" them, an error will occasionally happen regarding the assignment of their name to a range variable's Value property (also the Value2 and Default). The assignment comes from a checkbox type variable's .Name property, to be clear. When the error occurs, it is never the same checkbox, but it will persist until I delete the data it is referencing and restart the script, although this sometimes causes everything to crash. The data is stored on a special tab where I have 8 cells containing the UNIQUE formula to pull the names of certain companies from each tab. These company names have the checkboxes next to them, so that when a Listbox is used to select the name of one of the tabs, the company name can be assigned to that tab through my other code. I hope that makes sense.

Option Explicit

Public cBox(0 To 132) As CheckBox
Public stationRngs(0 To 8) As Range
Public oldRowCount(0 To 9) As Long
Public stationStr(0 To 8) As String
Public ownerStr(0 To 132) As String

Public Sub InitSettings()
  
    Dim wb As Workbook
    Dim WS(0 To 9) As Worksheet
    Dim optSht As Worksheet
  
    Dim rg(1 To 9) As Range
    Dim uniqueRng(0 To 16) As Range
  
    Dim baseRng As Range
    Dim stationRng As Range
  
    Dim i As Long, y As Long, x As Long
  
    Dim checkStr() As String
    Dim nameCheck As String
    Dim billerName(0 To 8) As String
  
    Dim c As Variant, sc As Variant, cb As Variant
  
    EventStop
    FindLastCell
  
    Set wb = ActiveWorkbook
    Set optSht = Sheets("Options")
    Set baseRng = optSht.Range("G34")
    Set stationRng = optSht.Range(wb.Names("Stations"))
  
    For Each cb In optSht.CheckBoxes
  
        cb.Enabled = True
        cb.Value = False
      
    Next

    For i = 0 To 9
        Debug.Print (i)
        Set WS(i) = wb.Sheets(Sheets(i + 1).Name)
        oldRowCount(i) = WS(i).UsedRange.Rows.Count
        ResortRange WS(i), WS(i).ListObjects.Item(1)
        If i < 9 Then
      
            x = 0
            Set uniqueRng(i) = baseRng.offset(i, 1).Resize(1, optSht.Range("XFC34").End(xlToLeft).Column - 8
            Set uniqueRng(i + 8) = baseRng.offset(i + 9, 0)
            Set stationRngs(i) = uniqueRng(i + 8).offset(0, 1)
            For Each c In uniqueRng(i)
              
                If stationStr(i) = "" And c.Value2 <> "" Then
              
                     stationStr(i) = c.Value2
                   
                ElseIf stationStr(i) <> "" And c.Value2 = "" Or c.Value = 0 Then
              
                    Exit For
                  
                Else
              
                    stationStr(i) = stationStr(i) & "," & c.Value2

                End If
              
            Next
            ReDim checkStr(0)
            checkStr = Split(stationStr(i), ",")

                For Each sc In stationRng

                    nameCheck = sc.Value2
        
                    If InStr(1, stationStr(i), nameCheck) > 0 Then
                        Set cBox(x) = optSht.CheckBoxes(sc.Cells(1, 2).Address)
                        ' Problem Line is next -----------------------------------------------------
                        uniqueRng(i + 8).offset(0, x + 1).Value2 = cBox(x).Name
                        ' Problem Line is above -----------------------------------------------------
                        cBox(x).Value = True
                        cBox(x).Enabled = False
                        ownerStr(i) = uniqueRng(i).Cells(1, 0).Value2
                        x = x + 1
                        Set stationRngs(i) = Union(stationRngs(i), uniqueRng(i + 8).Cells(1, x + 1))
                    End If
               
                Next
  
        End If

    Next
  
    For i = 1 To 9

        Set rg(i) = WS(i).Range("M:M")
        Set rg(i) = Union(rg(i), WS(i).Range("O:O"), WS(i).Range("Q:Q"), WS(i).Range("S:S"), WS(i).Range("U:U"))
        rg(i).EntireColumn.Hidden = True
      
    Next
  
    EventStart
  
End Sub

 

[RoryA]

What is the actual error message on that line?

 

[gravanoc]

Next time it occurs I'll post that info here, because I forgot to make note of the specific error. I'm paraphrasing, but it was something like a failure of the "Value" or "Default" or "Value2" property of the Range class I think, depending on which I tried to assign it to. I can't replicate it, it just decides to happen.

 

[gravanoc]

Ok it occurred again.

 

[RoryA]

Any merged cells or sheet protection?

 

[gravanoc]

There is one merged cell range on the sheet, but it isn't being referenced by anything. No sheet protection. This most recent occurrence happened after I re-assigned some of the station IDs (checkboxes) to a different tab. When it occurred, it again caused a crash when I deleted the old checkbox names from the range in question. After re-loading, I turned off events and deleted the old info, then restarted the script with no problems.