Transpose an array - type mismatch error

[Excelmasters]

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

 

[ParamRay]

Hello.

In my experience Application.Transpose() is prone to throwing errors when used in code. (You are actually calling an Excel function using VBA.)

A better alternative is to create a new array and use a couple of nested loops to copy the contents of the original array to the new array. You could write a helper method to do this and then pass the original array to it. (Use the same logic as in C#.)

 

[Excelmasters]

Hi Thank you for the reply.

these VBA syntax hurts me, do you have any sample code for this or can you please help me to manually write the array into worksheet. any help will be greatly appreciated. thanks in advance.

 

[Excelmasters]

Bumping to see if someone can help me on this ...

 

[Excelmasters]

No help ?

 

[Norie]

Instead of Application.Transpose why not use a loop/loops to write the data to the sheet?

PS Are you sure the error you are encountering is caused by having strings with more than 255 characters?

 

[ParamRay]

Hello.

In my experience Application.Transpose() is prone to throwing errors when used in code. (You are actually calling an Excel function using VBA.)

A better alternative is to create a new array and use a couple of nested loops to copy the contents of the original array to the new array. You could write a helper method to do this and then pass the original array to it. (Use the same logic as in C#.)

In reference to my original response, here are examples of how you might write helper methods in both C# and VBA. You would then pass your original array to the helper method as an argument, and get back its transposed copy in return. (These examples work with string arrays, but could easily be modified for use with other data types.)

C#

public static string[,] TransposeArray(string[,] originalArray)
{
    int rows = originalArray.GetLength(0);
    int cols = originalArray.GetLength(1);
    string[,] transposedArray = new string[cols, rows];

    for (int r = 0; r < rows; r++)
    {
        for (int c = 0; c < cols; c++)
        {
            transposedArray[c, r] = originalArray[r, c];
        }
    }

    return transposedArray;
}

VBA

Public Function TransposeArray(originalArray() As String) As String()
  Dim rows As Integer
  Dim cols As Integer
  Dim r As Integer
  Dim c As Integer
  
  rows = UBound(originalArray, 1)
  cols = UBound(originalArray, 2)
  ReDim transposedArray(cols, rows) As String
  
  For r = 0 To rows
    For c = 0 To cols
      transposedArray(c, r) = originalArray(r, c)
    Next c
  Next r
  
  TransposeArray = transposedArray
End Function