Type mismatch using split VBA

Question

I have the following code and it gives me the error Type mismatch for the line of code «Split_dt_2 = Split(Split_dt_1, ",")». I'm not able to run through the code with F8 because it gives me the error right away so i can't give the exact value of «Split_dt_1» but it's always a date which has that form : [11/1/2019,12/1/2019].

My goal would be to obtain : y_Dest = 2019 and m_Des = 11

 Sub import_Redeem_Spread()    
    Workbooks.Open "C:\Users\106400\OneDrive\Documents\FTT\CDOPT_AB.xlsm"
    Dim wksSource As Worksheet, wksDest As Worksheet
    Set wksSource = Workbooks("CDOPT_AB.xlsm").Sheets(2)
    Set wksDest = ThisWorkbook.Sheets(2)



 Dim Split_dt_1() As String
    Dim Split_dt_2() As String
    Dim Split_dt_3() As String
    Dim Split_dt_4() As String


    nbRows = wksSource.Cells(Rows.Count, 1).End(xlUp).Row
    nbDates = wksDest.Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To nbRows
        If wksSource.Cells(i, 16) = "CPG Taux Fixe" Then
            For m = 7 To nbDates
 Split_dt_1 = wksDest.Cells(m, 2)
        Split_dt_2 = Split(Split_dt_1, ",")
        Split_dt_3 = Split_dt_2(0)
        Split_dt_4 = Split(Split_dt_3, "[")
        y_Dest = Right(Split_dt_4(1), 4)
        m_Dest = Left(Split_dt_4(1), 2)
        y_source = Left(Cells(I, 3), 4)
        m_Source = Right(Cells(I, 3), 2)

                If y_Dest = m_Dest & y_Source = m_Source Then
                    For n = 4 To 15
                        wksDest.Cells(m, n) = wksSource.Cells(i, n)
                    Next n
                End If
            Next m
        End If
    Next i

    End Sub

I tried «Dim Split_dt_2() As Variant» but it does noes solve the problem

and I tried

Split_dt_1 = wksDest.Cells(m, 2).value
Split_dt_2 = Split(Split_dt_1, ",")

and it still doesn't work

Thanks in advance!

Answer 1

Use a Variant when using Split to create the array instead of Diming it as a String array.

A Variant will take on the properties of an Array when the function you are using returns an Array.

Dim Split_dt_1 As Variant
Split_dt_1 = Split(wksDest.Cells(m, 2), ",")