Transpose an array - type mismatch error

Excelmasters

Board Regular
Joined
Jun 11, 2015
Messages
115
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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#.)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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#
Code:
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
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top