CODEWITHSUNDEEP
excelvbatype-mismatch
Jamie Marshall
Jamie Marshall

Reputation: 2294

VBA Mis-Match Error 13

I'm relatively new to VBA, I wrote a Sub to parse CSV information that is contained in a single cell and return certain pieces of data to other cells in the spreadsheet. Its a relatively simple code but I can't find the error. Its a Err 13 Mismatch on the line Moods = Application.Index(fullArray, 0, 3) Any help would be greatly appreciated.

Private Sub parseCSV()
'Parse "Notes" column and return Moods/Keywords to their apropriate cells

    Dim CSV As String
    Dim fullArray As Variant
    Dim lRow As Long
    Dim Keywords As Variant
    Dim Moods As Variant
    Dim i As Long

    lRow = ActiveSheet().Range("BL" & ActiveSheet().Rows.Count).End(xlUp).Row

    For i = 3 To lRow

        CSV = ActiveSheet.Range("BL" & i)            
        fullArray = Split(CSV, Chr(10))
        Moods = Application.Index(fullArray, 0, 3)
        Keywords = Application.Index(fullArray, 0, 2)

        ActiveSheet.Range("CD" & i).Value = Moods
        ActiveSheet.Range("CE" & i).Value = Keywords

    Next i

End Sub

I tested the parse function below:

Private Sub parseCSVTest()
'Parse "Notes" column and return Moods/Keywords to their apropriate cells

    Dim CSV As String
    CSV = ActiveSheet.Range("BL5")

    Dim fullArray As Variant
    fullArray = Split(CSV, Chr(10))

    Dim Moods As Variant
    Moods = Application.Index(fullArray, 0, 3)

    Dim Keywords As Variant
    Keywords = Application.Index(fullArray, 0, 2)

    ActiveSheet.Range("CD5").Value = Moods
    ActiveSheet.Range("CE5").Value = Keywords

End Sub

Works just fine. So I tested my loop.

Private Sub loopTest()

    Dim i As Long
    Dim lRow As Long
    lRow = ActiveSheet().Range("BL" & ActiveSheet().Rows.Count).End(xlUp).Row


    For i = 3 To lRow
        ActiveSheet.Range("CD" & i).Value = "testing"
    Next i

End Sub

Works fine. I don't know where to go from here.

Upvotes: 0

Views: 303

Answers (2)

Jamie Marshall
Jamie Marshall

Reputation: 2294

I Got it. Thanks for all the help everyone, this was a brain buster. For some reason 

Moods = Application.Index(fullArray, 0, 3)
Keywords = Application.Index(fullArray, 0, 2)

won't return a value while specifying an entire column ( (0, 0) = all rows in columns in VBA Arrays ). I simply changed the array references to be (1, 3) and (1, 2). The full working code is below. This is the simplest way I've found online to parse to data from a cell and slice the array. Thanks to everyone that helped with this. 

Private Sub parseCSV()
'Parse "Notes" column and return Moods/Keywords to their apropriate cells

    Dim CSV As String
    Dim fullArray As Variant
    Dim lRow As Long
    Dim Keywords As Variant
    Dim Moods As Variant
    Dim i As Long

    lRow = ActiveSheet().Range("BL" & ActiveSheet().Rows.Count).End(xlUp).Row

    For i = 3 To lRow

        CSV = ActiveSheet.Range("BL" & i)
        fullArray = Split(CSV, Chr(10))
        Moods = Application.Index(fullArray, 1, 2)
        Keywords = Application.Index(fullArray, 1, 3)

        ActiveSheet.Range("CE" & i).Value = Moods
        ActiveSheet.Range("CD" & i).Value = Keywords

    Next i


End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

You are using CSV before you have assigned a value to it.

Upvotes: 1

Related Questions