VBA Excel: Runtime error 13 when getting range content into an array - long cell content

Question

I am trying to get the content of a row into a 1 dimensional VBA array. I used the very basic way to get the content of the row, by:

1- assigning the range content to an Excel Variant

2- transposing the array twice, to get it from 2 dimensional to 1 dimensional

This works fine in almost all my lines, but I am getting a runtime error 13 'Type mismatch' when one of the cells has a content longer than 255 caracters.

Do you by any chance have any advice on how to solve this issue?


Dim rowContent As Variant 
Dim lineCount As Long
Dim curLine As Long

lineCount = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(editWSName).Range("A:A")) - 2
For curLine = 3 To lineCount + 2
        rowContent = Application.Transpose(Application.Transpose(ThisWorkbook.Sheets(editWSName).Range(Cells(curLine, 2), Cells(curLine, colCount))))
        ...
Next curLine

Don't use Transpose. Create another array yourself and populate it from the first one. — Rory
– Rory, Commented Jun 7, 2019 at 13:23

Gary's Student · Accepted Answer · 2019-06-07 15:35:03Z

1

Here is a very simple way to get the content of a single row into a one-dimensional array:

Sub RowToArray()
    Dim rng As Range, s As String
    Dim msg As String

    Set rng = Rows("1:1").Cells
    s = Chr(1)

    With Application.WorksheetFunction
        arr = Split(.TextJoin(s, True, rng), s)
    End With

    msg = LBound(arr) & vbCrLf & UBound(arr)
    For Each a In arr
        msg = msg & vbCrLf & a
    Next a

    MsgBox msg
End Sub

As you see, cell D1, which contains more than 300 characters, is fully captured.

answered Jun 7, 2019 at 15:35

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pierre Massé Over a year ago

Thanks Gary! I did not use your solution for my specific problem, but I have bookmarked it for further usage. Regards,

Pierre Massé · Accepted Answer · 2019-06-07 15:38:26Z

So I managed to get through this problem as recommended by Rory, I added a pivot array to populate my final array.

My solution is the following:

    Dim lineCount As Long
    Dim colCount As Long
    Dim tempArray As Variant ' tableau temporaire
    Dim rowContent() As String ' array contenant le contenu de la ligne courante
    Dim i As Long
    ...

    lineCount = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(editWSName).Range("A:A")) - 2
    colCount = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(editWSName).Range("2:2"))
    fieldCodes = Application.Transpose(Application.Transpose(ThisWorkbook.Sheets(editWSName).Range(Cells(2, 2), Cells(2, colCount))))
    ReDim rowContent(1 To colCount - 1)

    ...

    For curLine = 3 To lineCount + 2
        tempArray = ThisWorkbook.Sheets(editWSName).Range(Cells(curLine, 2), Cells(curLine, colCount))
        For i = 1 To UBound(tempArray, 2)
            rowContent(i) = tempArray(1, i)
        Next i
        Set controlDict = controlLine(rowContent, fieldCodes)
        ...
    Next curLine

Collectives™ on Stack Overflow

VBA Excel: Runtime error 13 when getting range content into an array - long cell content

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related