VBA Runtime Error '9', Subscript out of range error

[chrisnash]

Hello Everyone,

I hope you are well,

So I have a form that has been working perfectly, but I have added some more code to it and now I am getting the Runtime Error 9 coming up.

The form writes to an another workbook and the below still does that but then throws up an error.

 Dim trtt_label() As MSForms.Label
Dim trt_box() As MSForms.ComboBox

        If (trt_box1.Value <> "") Then            TRTTRows = .Cells(.Rows.Count, "AA").End(xlUp).Row + 1
            .Cells(TRTTRows, "AA").Value = DTPicker1.Value
            .Cells(TRTTRows, "AB").Value = trt_box1.Value
            .Cells(TRTTRows, "AC").Value = Shift_ComboBox.Value
            
            If TRTTRows > 1 Then
                For I = 2 To TRTTRows
                   If (trt_box(I).Value <> "") Or (Shift_ComboBox.Value <> "") Then   ***THIS IS THE LINE THAT IS HIGHLIGHTED
                        TRTTRows = TRTTRows + 1
                        .Cells(TRTTRows, "AA").Value = DTPicker1.Value
                        .Cells(TRTTRows, "AB").Value = trt_box(I).Value
                        .Cells(TRTTRows, "AC").Value = Shift_ComboBox.Value
                    End If
                Next I
            End If
        End If

*****the code that adds boxes dynamically in a form*****

Private Sub btnNextTRTT_Click()'ErrorHandler
    Dim LeftAdjust As Long
    Dim TopAdjust As Long
    
    'Add the next row of boxes
    TRTTRows = TRTTRows + 1
    ReDim Preserve trtt_label(TRTTRows)
    ReDim Preserve trt_box(TRTTRows)
   
    
    If TRTTRows < 4 Then
        LeftAdjust = 0
        TopAdjust = (TRTTRows - 1) * 20
    ElseIf TRTTRows < 7 Then
    
        LeftAdjust = 168
        TopAdjust = (TRTTRows - 4) * 20
        
        Else
        LeftAdjust = 336
        TopAdjust = (TRTTRows - 7) * 20
    End If


et trtt_label(TRTTRows) = MultiPage2.Pages("Page4").Controls.Add("Forms.Label.1", "trtt_label" & TRTTRows)
    trtt_label(TRTTRows).Caption = "Please Select"
    trtt_label(TRTTRows).Left = 168 + LeftAdjust
    trtt_label(TRTTRows).Top = 198 + TopAdjust
    trtt_label(TRTTRows).Width = 72
    
    Set trt_box(TRTTRows) = MultiPage2.Pages("Page4").Controls.Add("Forms.ComboBox.1", "trt_box" & TRTTRows)
    trt_box(TRTTRows).Left = 252 + LeftAdjust
    trt_box(TRTTRows).Top = 198 + TopAdjust
    trt_box(TRTTRows).Width = 72
 
    trt_box(TRTTRows).AddItem "ABC1"
    trt_box(TRTTRows).AddItem "ADE2"
    trt_box(TRTTRows).AddItem "ADG3"
  
    
            
            
                    
   
  If TRTTRows > 8 Then
       btnNextTRTT.Enabled = False
    End If
 
    
End Sub

 

[BiocideJ]

The first place I would inspect would be the trt_box ComboBox list items.
I suspect it has fewer items in it then are being returned from this ---> .Cells(.Rows.Count, "AA").End(xlUp).Row + 1

 

[Norie]

Where do you get the error?

 

[chrisnash]

I get the error at

[/COLOR][COLOR=#333333]If (trt_box(I).Value <> "") Or (Shift_ComboBox.Value <> "") Then[/COLOR][COLOR=#333333]

but the actual part that is highlighted is

[/COLOR][COLOR=#333333]trt_box(I).Value[/COLOR][COLOR=#333333]

 

[BiocideJ]

Have you compared the source range that I mentioned earlier with the source of the combobox?
specifically, how many rows are being returned in each. It appears that there are more rows of data in column AA than are in the source of the combobox.

So basically, if AA has 10 items and the combobox has only 9, when I becomes 10 you are looking for the 10th item in the combobox which doesn't exist and throws an error.