Handling string in an array of length greater then 255 in excel

[Admin]

Hi,

PFA.

[LalitPandey87]

Hi,

PFA.

Hi Admin,

There is an issuse when = comes at first then excel treat it as a formula and raise an error.
I modified code and replaced = with '=.
Here is the modified version:-

Sub kTest()
    
    Dim ka, k(), c() As String, i As Long
    Dim j As Long, n As Long, r As Range, s As String
    
    Application.ScreenUpdating = False
            
    Set r = Sheet1.Range("a1").CurrentRegion
    ka = r
    
    ReDim k(1 To UBound(ka, 1), 1 To UBound(ka, 2))
    
    For i = 1 To UBound(ka, 1)
        For j = 1 To UBound(ka, 2)
            If Len(ka(i, j)) > 255 Then
                n = n + 1
                ReDim Preserve c(1 To n)
                c(n) = Cells(i, j).Address(0, 0)
            Else
                If Left$(ka(i, j), 1) = Chr(61) Then
                    k(i, j) = Chr(39) & ka(i, j)
                Else
                    k(i, j) = ka(i, j)
                End If
            End If
        Next
    Next
    
    With Sheet2.Range("a1")
        .Parent.UsedRange.ClearContents
        .Resize(UBound(k, 1), UBound(k, 2)) = k
        If n Then
            For i = 1 To n
                s = r.Range(c(i)).Value
                If Left$(s, 1) = Chr(61) Then s = Chr(39) & s
                .Range(c(i)) = s
            Next
        End If
    End With
    Application.ScreenUpdating = True
End Sub