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?

 

[mikerickson]

I'm guessing then that the delimiter in the cells might not be the normal [space][dash][space].
Have you checked that the delimiters in the cells are the same as the delimiters in the Split statement? There are funny dashes and spaces that can be a pain.

Have you stepped through the code to verify that CDate is the value that you think it is?

 

[CrispyAsian]

@mikerickson, funny that you should say that. The format of the cells is actually input by a module running on sheet2. So I know for sure the delimiter is " - " because that's what I put into the first code to input them that way.

 

[CrispyAsian]

@JackDanIce

Your code works perfectly, except it doesn't take into account for cells that are blank. So as soon as it reaches a cell that is blank it throws up a Runtime Error 1004. Which is fine because I need it to stop if reaches a blank cell anyway, but I don't want my end user to think that something is wrong when the error comes up. Also, your code only displays the first date that is split. I need the dates that are on both sides of the split in different cells.

 

[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
            If Len(c.Value) > 0 And InStr(c.Value, " - ") Then
                SDates = Split(Sheets("Sheet2").Cells(2, c).Value, " - ")
                Sheets("Sheet1").Cells(21, 1).Resize(, UBound(SDates) + 1).Value = SDates
                Erase SDates
            End If
        Next c
End Select

Application.ScreenUpdating = True

End Sub

 

[CrispyAsian]

@JackDanIce

This throws out an Compile Error "Invalid Qualifier" on the Len(c.Value)

 

[Rick Rothstein]

@JackDanIce

This throws out an Compile Error "Invalid Qualifier" on the Len(c.Value)

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
            If Len(c.Value) > 0 And InStr(c.Value, " - ") > 0 Then
                SDates = Split(Sheets("Sheet2").Cells(2, c).Value, " - ")
                Sheets("Sheet1").Cells(21, 1).Resize(, UBound(SDates) + 1).Value = SDates
                Erase SDates
            End If
        Next c
End Select

Application.ScreenUpdating = True

End Sub

Try putting the red highlighted text I show above in JackDanIce's code above (once you have an And or Or operator, I think you have to have full logical expressions for it to work on).

 

[JackDanIce]

@Rick Rothstein, I thought 0 means FALSE else any positive number as TRUE. Wasn't aware if using AND or OR you had to fully include the comparison, thanks!

Just confirmed in debug window:

?(Len("ABC") > 0 and Instr("ABC","C")>0) = True

Prints True

However.

?(Len("ABC") > 0 and Instr("ABC","C")) = True

Prints False

 

[Rick Rothstein]

@Rick Rothstein, I thought 0 means FALSE else any positive number as TRUE. Wasn't aware if using AND or OR you had to fully include the comparison, thanks!

If..Then looks for False (0) or Not False (any non-zero value), but And, Or, Xor, Imp and Eqv (you probably never even heard of those last two) actually compare logicals (True and/or False).

 

[JackDanIce]

So it's almost a data matching issue i.e. booleans against numbers (non-booleans) that causes it to require only TRUE or FALSE if you're using additional logical operators?

Yes, never heard of IMP and EQV; I'll refrain from asking what and go do some research! But only because it's not strictly speaking related to this thread , thank you Rick.

 

[Rick Rothstein]

Yes, never heard of IMP and EQV; I'll refrain from asking what and go do some research!

Probably not worth the effort researching them... they are quite odd... in my 35+ years of programming in BASIC (the predecessor to VB), yes Imp and Eqv were available back then) and VB/VBA, I think I found a use for one of them (don't remember which) only one time.