Writing an Array to Range causes error

[bbrother]

Dear Experts,

I have a large array I need to dump to a range. I read that there are limits on the size of array you can dump, so I wrote this sub to prevent writing too much to the sheet at once. I still get an error when writing row 1385, column 11. This item of data is a string that is only about 75 characters in length.

In fact, I have isolated the problem such that I can't even assign the individual element of the array to an individual cell. In the immediate window I used:

myrange(85,11)=subsnap(85,11)

which causes the run time error 1004: Application-defined or object-defined error.

In the immediate window I have:

? typename(subsnap(85,11))
String
? len(subsnap(85,11))
 76 
? subsnap(85,11)=CleanTrim(subsnap(85,11))
True

Note that clean trim is a sub that clears out all the special characters of the string. I am not sure if trying to put a special character in a cell would cause an issue anyhow.

So, here's my sub. Don't laugh too hard as I am still a VBA novice.

Sub RangeDump(Snapshot() As Variant)
    'Snapshot is an array roughly 10000 x 50, some elements in the array are long strings
    ' Row is used in a loop to increment through the array
    ' MyRange is used to define a range on the spreadsheet to dump part of the array
    ' SubSnap is a subset of the full snapshot array
    ' Index is a counter to limit the size of the subset array
    ' col is used in a loop to increment the columns of the array
    
    Dim Row As Long
    Dim MyRange As Range
    Dim SubSnap(1 To 100, 1 To 100) As Variant
    Dim Index As Long
    Dim Col As Integer
    
    'dumping to Sheet 2 until this is debugged
    With ThisWorkbook.Sheets("Sheet2")
        'setting the range equal to the size of the subset array.  I thought this was clever
        Set MyRange = Range(.Cells(1, 1), .Cells(UBound(SubSnap, 1), UBound(SubSnap, 2)))
    End With
    Index = 1
    
    For Row = 1 To UBound(Snapshot, 1)
        For Col = 1 To UBound(Snapshot, 2)
            SubSnap(Index, Col) = Snapshot(Row, Col)
        Next Col
        
        If Index = UBound(SubSnap, 1) Then
            'hit the limit of what subsnap can hold - dump it
            MyRange = SubSnap()
            Set MyRange = MyRange.Offset(Index, 0)
            Erase SubSnap
            Index = 0
        End If
        Index = Index + 1
        
    Next Row
    
    MyRange.RowHeight = 30
    Stop
End Sub

Any help would be greatly appreciated!! Thank you for your time.

 

[Norie]

What do you get when you enter this in the immediate window?

? subsnap(85, 11)

 

[bbrother]

Well the entire contents of the cell are a little sensitive, but I was able to replicate the problem using the 2 leftmost characters:

myrange(85,11)=subsnap(85,11) 'fail
myrange(85,11)=left(subsnap(85,11),2) 'fail
myrange(85,11)=left(subsnap(85,11),1) 'success


? left(subsnap(85,11),2)
=>

? asc(mid(subsnap(85,11),1,1))
 61 
? asc(mid(subsnap(85,11),2,1))
 62 
? asc(mid(subsnap(85,11),3,1))
 32 
? typename(subsnap(85,11))
String
? typename(subsnap)
Variant()
? typename( myrange(85,11))
Range

 

[Norie]

Looks to me as though Excel is interpreting the value in subsnap(85,11) as a formula.

If you are dealing with strings then try formatting the destination cells as Text before populating them.

 

[bbrother]

Wow... ok I feel dumb now - that was it! I didn't suspect the problem was in my worksheet instead of my code.

Thank you Norie for your fast and accurate response