如何在Excel中的单元格中处理超过255个字符？

I'm using VBA code to copy paste (Application.Transpose) some information from sheet 2 to sheet 1. The code works pretty well, however, I have several cells with more than 255 characters.

我正在使用VBA代码将粘贴(Application.Transpose)从表2复制到表1中。代码工作得很好,但是,我有几个单元格超过255个字符。

I encounter an error with these cells (error 13: type mismatch). I think it dues to the fact that the cell contains more than 255 characters.

我遇到这些单元格的错误(错误13:类型不匹配)。我认为这是因为单元格包含超过255个字符。

Below, the code that I'm using:

下面,我正在使用的代码:

Sub Bouton3_Cliquer()

Dim O1 As Worksheet 'déclare la variable O1 (Onglet 1)
Dim O2 As Worksheet 'déclare la variable O2 (Onglet 2)
Dim TC As Variant 'déclare la variable TC (Tableau de Cellules)
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)
Dim TMP As Variant 'déclare la variable TMP (tableau TeMPoraire)
Dim TL() As Variant 'déclare la variable TL (Tableau de Lignes)
Dim K As Integer 'déclare la variable K (incrément)
Dim PL As Integer 'déclare la variable PL (Première Ligne)
Dim J As Integer 'déclare la variable J (incrément)
Dim L As Byte 'déclare la variable L (incrément)

Set O1 = Sheets("Feuil1") 
Set O2 = Sheets("evaluations") 
TC = O1.Range("B1").CurrentRegion

Set D = CreateObject("Scripting.Dictionary") 

For I = 4 To UBound(TC, 1) 
    D(TC(I, 1)) = "" 
Next I 
TMP = D.keys 
TC = O2.Range("A1").CurrentRegion 
For I = 0 To UBound(TMP, 1) 
    Erase TL 'efface le tableau TL
    K = 1 'initialise la variable K

    PL = O1.Columns(2).Find(TMP(I), O1.Range("B3"), xlValues, xlWhole).Row
    For J = 2 To UBound(TC, 1) 
            If TC(J, 1) = TMP(I) Then
            ReDim Preserve TL(1 To UBound(TC, 2) - 1, 1 To K)
            For L = 1 To UBound(TC, 2) - 1 
                TL(L, K) = TC(J, L + 1) 
            Next L 
            K = K + 1 '
        End If 'fin de la condition
    Next J 
            If K > 1 Then O1.Cells(PL, 16).Resize(UBound(TL, 2), UBound(TL, 1)).Value = Application.Transpose(TL)
Next I '

End Sub

And the problematic line:

而有问题的路线:

If K > 1 Then 
  O1.Cells(PL, 16).Resize(UBound(TL, 2), _
      UBound(TL, 1)).Value = Application.Transpose(TL)

Do you have any idea how I can resolve this issue?

你知道我怎么解决这个问题吗?

1 个解决方案

#1

Change your monoline statement

改变你的monoline声明

If K > 1 Then O1.Cells(PL, 16).Resize(UBound(TL, 2), UBound(TL, 1)).Value = Application.Transpose(TL)

to this multiline and declare T_TL as variant too

到这条多线并将T_TL声明为变体

If k > 1 Then
    T_TL = Application.Transpose(TL)
    O1.Cells(PL, 16).Resize(UBound(TL, 2), UBound(TL, 1)).Value = T_TL
End If

With this you will see if it is the Transpose or the assigning part that doesn't work!

有了它,您将看到它是Transpose还是分配部分不起作用!

#1