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?

 

[mumps]

Try:

Dim SDates() As Variant

 

[JackDanIce]

Try:

Private Sub ComboBox2_Change()

Dim SDates() As String
Dim c As Long

Application.ScreenUpdating = False

Range("F3:PO3").Clear

Select Case ComboBox2
    Case Is = "AF CTSAC"
        For c = 2 To 52
            SDates = Split(Sheets("Sheet2").Cells(2, c).Value, " - ")
            Sheets("Sheet1").Cells(21, 1).Resize(, UBound(SDates)).Value = SDates
            Erase SDates
        Next c
End Select

Application.ScreenUpdating = True

End Sub

 

[mikerickson]

This will insure that you have enough members of the Split

SDates = Split(CDates & " -  - ", " - ")

Note the two spaces between the added dashes and the leading & trailing spaces.
The code is appending two delimiters to the end of CDates.

 

[Fluff]

What is the data in your range

 

[CrispyAsian]

Hey @mumps,

See I thought that might be the issue as well. But when I do that I get a Error 13: Type Mismatch on the Split line. I don't know if that's because a Variant can't be made into an array or what the problem with that is but for now I'll keep it as a string.

 

[FormR]

I get a subscript error on the .Value lines.

Hi, when you get the error - what is the value of CDates? What is the upper bound of SDates?

You can find out by using these in the immediate window.

? CDates

? UBound(SDates)

It's possible that "CDates" is empty/blank.

 

[CrispyAsian]

@Fluff, it is cells each containing two dates. They look like this in the cell: 1/2/2018 - 2/3/2018. I need the two dates separated so I thought Split using the " - " deliminator would be the best way to do that.

 

[mikerickson]

The code I posted should protect you against errors, but I notice that you are looping through 50 cells, doing the split and then writing the results to the same cells. Your cells will contain only the results for AZ2, overwriting all the previous Splits.

 

[CrispyAsian]

@mikerickson, haha yah I know rewriting those cells. That was just because I'm going to put it into different cells, I just wanted to keep the code as simple as possible to post it. So you were right though, your code did get rid of the error. However whenever I run the code now, nothing shows up in the cells. I even stepped through it and it never shows any values getting put into those two cells.