PDA

View Full Version : Transpose an array - type mismatch error



PENNAIAH
07-16-2018, 01:20 AM
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
07-16-2018, 02:30 AM
Can you provide some sample data which demonstrates the error?

PENNAIAH
07-16-2018, 02:42 AM
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
07-16-2018, 03:14 AM
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
07-16-2018, 03:52 AM
Hi Thanks for the reply and i have tried to say Var as variant but no help...:(:(

Jan Karel Pieterse
07-16-2018, 05:26 AM
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
07-16-2018, 05:34 AM
Hi problemm is transpose doesnt accept if any cell value is more than 255 char.

georgiboy
07-16-2018, 06:12 AM
Also why transpose twice in your original code?


Application.Transpose(Application.Transpose(Var))

Jan Karel Pieterse
07-16-2018, 07:28 AM
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.