Subscript Error when using Split

CrispyAsian

Board Regular
Joined
Sep 22, 2017
Messages
64
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.

Code:
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?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,682
Office Version
  1. 365
Platform
  1. Windows
Try:
Code:
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
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
This will insure that you have enough members of the Split

Code:
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What is the data in your range
 

CrispyAsian

Board Regular
Joined
Sep 22, 2017
Messages
64
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

Code:
? CDates

Code:
? UBound(SDates)

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

CrispyAsian

Board Regular
Joined
Sep 22, 2017
Messages
64
@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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
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

Board Regular
Joined
Sep 22, 2017
Messages
64
@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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,519
Messages
5,523,356
Members
409,513
Latest member
TibiBenyi

This Week's Hot Topics

Top