Transpose an array - type mismatch error

[PENNAIAH]

Hi All,

i am not so great VBA as i am from C# background and stuck in this below issue from couple of days.

i know this error occurs when a cell value is more than 255 characters , unfortunatly am not able to come over of it.

can someone please help me to tweak this code. (error occurs at application .transpose)

Dim DT


        ar = SWB.Sheets("Temp").Cells(1, 1).CurrentRegion.Value
  Set DT = CreateObject("Scripting.Dictionary")
    With DT
        .CompareMode = 1
        ReDim v(1 To UBound(ar, 2))
        For i = 2 To UBound(ar, 1)
            For n = 1 To UBound(ar, 2)
                str = str & Chr(2) & ar(i, n)
                v(n) = ar(i, n)
            Next
            If .exists(str) Then
                .Item(str) = Empty
            Else
                .Item(str) = v
            End If
            str = ""
        Next
        
        
        
        


       Dim nws As Worksheet
       Resultwb.Activate
       
 Set nws = Resultwb.Worksheets.Add(After:=Worksheets(Worksheets.Count))
 
        For Each arr In .keys
            If IsEmpty(.Item(arr)) Then .Remove arr
        Next
        Var = .Items: j = .Count
        
  
   End With


    With nws.Range("a1").Resize(, UBound(ar, 2))
        .CurrentRegion.ClearContents
        .Value = ar
        If j > 0 Then
             .Offset(1).Resize(j).Value = Application.Transpose(Application.Transpose(Var))
        
       
            
        End If

[Jan Karel Pieterse]

Can you provide some sample data which demonstrates the error?

[PENNAIAH]

Hi Jan,

thanks for the reply, am not sure how to put the data here, but you can test with any data just putting any cell value more than 255 character and code fails at application.transpose (type mismatch)

[georgiboy]

My guess would be that var is either defined as string or has defaulted that way due to not being defined.

Try defining var as Variant and see if this helps, if I remember correctly the max string length is 255 characters.

Hope this helps

[PENNAIAH]

Hi Thanks for the reply and i have tried to say Var as variant but no help...

[Jan Karel Pieterse]

If I add these declarations:

    Dim DT
    Dim ar As Variant
    Dim arr As Variant
    Dim var As Variant
    Dim j As Long
    Dim i As Long
    Dim n As Long
    Dim Str As String

it runs without a hitch up to a length of 32767 characters in any cell.

[PENNAIAH]

Hi problemm is transpose doesnt accept if any cell value is more than 255 char.

[georgiboy]

Also why transpose twice in your original code?

Application.Transpose(Application.Transpose(Var))

[Jan Karel Pieterse]

I have tested your code with my additions and it runs without errors for string lengths up to and including 32767 characters. SO the 255 character limit is not correct.