Transpose an array - type mismatch error

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)

Code:

---

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

---

Can you provide some sample data which demonstrates the error?

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)

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

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

If I add these declarations:

Code:

---

    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.

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

Also why transpose twice in your original code?

Code:

---

Application.Transpose(Application.Transpose(Var))

---

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.