Subscript Error when using Split

[CrispyAsian]

Hey all,

I'm working on this code but everytime I run it, I get a subscript error on the .Value lines. I don't know what I'm doing wrong.

Private Sub ComboBox2_Change()


Range("F3:PO3").Clear


Dim SDates() As String
Dim CDates As String
Dim c As Range


Select Case ComboBox2
    Case Is = "AF CTSAC"
        For Each c In Worksheets("Sheet2").Range("B2:AZ2")
            If Not c Is Nothing Then
            CDates = Cells(2, c.Column).Value
            SDates = Split(CDates, " - ")
            Worksheets("Sheet1").Cells(21, 1).Value = SDates(0) 'These two lines are where I get the Subscript out of range error
            Worksheets("Sheet1").Cells(21, 2).Value = SDates(1)

            Else
            End If
        Next c
        
End Select


End Sub

I have the Select Case in there because I am going to be adding more option to what ComboBox2 could be equal to, but I want to get this part fixed before I do so. Anybody see what I'm not?

 

[JackDanIce]

EQV seems interesting but wondered where you'd ever use IMP, should have deferred to "not worth effort researching them"! Thanks again

 

[CrispyAsian]

Well, this thread got a lot busier than I expect! But @Rick Rothstein, I tried your fix, it still showed the same error. It particularly doesn't like the "c" in "c.Value".

 

[JackDanIce]

That was my fault through an earlier change. Taking Rick's suggestion into account, try:

Private Sub ComboBox2_Change()

Dim SDates() As String
Dim y   As Long
Dim x   As Long

Application.ScreenUpdating = False

Range("F3:PO3").Clear
x = 21

Select Case ComboBox2
    Case Is = "AF CTSAC"
        With Sheets("Sheet2")
            For y = 2 To 52
                If Len(.Cells(2, y).Value) > 0 And InStr(.Cells(2, y).Value, " - ") Then
                    SDates = Split(Sheets("Sheet2").Cells(2, y).Value, " - ")
                    Sheets("Sheet1").Cells(x, 1).Resize(, UBound(SDates) + 1).Value = SDates
                    x = x + 1
                    Erase SDates
                End If
            Next y
        End With
End Select

Application.ScreenUpdating = True

End Sub

 

[CrispyAsian]

@JackDanIce So when trying this code, it literally did nothing. So I stepped into it to try to see why, ever time it jumps straight to the end if...so I guess nothing is matching the If statement?

 

[JackDanIce]

Try breaking out the AND part of that IF statement and see if you can narrow the cause down:

Private Sub ComboBox2_Change()

Dim SDates() As String
Dim y   As Long
Dim x   As Long

Application.ScreenUpdating = False

Range("F3:PO3").Clear
x = 21

Select Case ComboBox2
    Case Is = "AF CTSAC"
        With Sheets("Sheet2")
            For y = 2 To 52
                If Len(.Cells(2, y).Value) > 0 Then
                    If InStr(.Cells(2, y).Value, " - ") Then
                        SDates = Split(Sheets("Sheet2").Cells(2, y).Value, " - ")
                        Sheets("Sheet1").Cells(x, 1).Resize(, UBound(SDates) + 1).Value = SDates
                        x = x + 1
                        Erase SDates
                    End If
                End If
            Next y
        End With
End Select

Application.ScreenUpdating = True

End Sub

Does your combo box selection match the select statement value?

 

[Rick Rothstein]

@JackDanIce So when trying this code, it literally did nothing. So I stepped into it to try to see why, ever time it jumps straight to the end if...so I guess nothing is matching the If statement?

That is because JackDanIce omitted the ">0" that I noted was needed. Try this (his code with the ">0" included)...

Private Sub ComboBox2_Change()

Dim SDates() As String
Dim y   As Long
Dim x   As Long

Application.ScreenUpdating = False

Range("F3:PO3").Clear
x = 21

Select Case ComboBox2
    Case Is = "AF CTSAC"
        With Sheets("Sheet2")
            For y = 2 To 52
                If Len(.Cells(2, y).Value) > 0 And InStr(.Cells(2, y).Value, " - ") [B][COLOR="#FF0000"]> 0[/COLOR][/B] Then
                    SDates = Split(Sheets("Sheet2").Cells(2, y).Value, " - ")
                    Sheets("Sheet1").Cells(x, 1).Resize(, UBound(SDates) + 1).Value = SDates
                    x = x + 1
                    Erase SDates
                End If
            Next y
        End With
End Select

Application.ScreenUpdating = True

End Sub

 

[JackDanIce]

D'oh!

 

[CrispyAsian]

@JackDanIce so you're a genius and I was being really dumb. I moved all my data to row 3 because I had to add an extra row to sheet 2 and just forgot about it. So it was running on a completely blank row which is why it wasn't displaying anything. Your code works magnificently. I can't thank you enough for all the help!

 

[CrispyAsian]

@Rick Rothstein, apparently it works without the second > 0!

 

[JackDanIce]

You're welcome and offshoot thanks @Rick Rothstein for the logic related operators stuff