Error copying more than 8202 characters from one cell to another

Question

Problem . I have about 8202 characters when the cell says Range ("A1").

Now I would like to copy the contents of cell (A1) to cell (A2) using VBA. I use below code

Sheets("XYZ").Range("A2") = Sheets("XYZ").Range("A1") 

After executing the code. It gives "A specific application or error defined by an object!"

Please help / help with your expert comments.

Observation . If I reduce the length of cell "A1" to 8202 or less, then the code works!

I am embarrassed. Help Pls.

Answer 1

Change your code to

 Sheets("XYZ").Range("A2") = Sheets("XYZ").Range("A1").Value 

and it will work.

Not quite sure why, since .Value is the default property for a range.

Answer 2

I was able to repeat your mistake with the following:

 Sub Test8202Copy() Dim wks As Worksheet Set wks = Worksheets("Sheet1") Dim x As String For i = 0 To 8202 x = x + "a" Next i wks.Range("A1").Value = x wks.Range("A2") = wks.Range("A1") End Sub 

---

I managed to resolve this error by adding a value to the copy.

 Sub Test8202Copy() Dim wks As Worksheet Set wks = Worksheets("Sheet1") Dim x As String For i = 0 To 8202 x = x + "a" Next i wks.Range("A1").Value = x wks.Range("A2").Value = wks.Range("A1").Value End Sub 

---

Using an intermediate variable without using .Value seems to work:

 Dim y As Variant y = wks.Range("A1") wks.Range("A2") = y 

So far, I guess that 8202 exceeds the character limit for the data type, which is used when you do not define .Value . The cell length in the cell is 32,767 (MS Excel 2010), which is almost 4x the value of 8201, which is cleared.

Answer 3

@Chris Neilsen provided the most practical and elegant solution to the problem (his code snippet follows):

 Sheets("XYZ").Range("A2") = Sheets("XYZ").Range("A1").Value 

In order to investigate and understand the possible cause of this strange behavior (maybe an error) of the Range object, I posted a couple of comments, which are given below:

There is a conceptual difference between the original expression (see below):

 Sheets("XYZ").Range("A2") = Sheets("XYZ").Range("A1") 

and the solution suggested by @ Chris Neilsen , namely: the original expression implicitly assigns a Range var object (essentially a pointer) to another Range object, as shown in the following code fragment with an explicit purpose

 Set rng = Sheets("XYZ").Range("A1") Sheets("XYZ").Range("A2") = rng 

while the proposed solution explicitly passes the value property. However, the reason the assignment of the Range object did not work for the value with the string. String lengths> 8202 are currently unclear (this may be caused by some internal nuances of the implementation of the Excel range object).

Thank you very much for posting this interesting question and fruitful discussion. Yours faithfully,

Answer 4

This limit (see re excel-2007 below) is covered in this MSDN article, although it is interesting that it is understood that the vba array is used

Separately, in fooobar.com/questions/974118 / ... excel-2003 cant processes array strings longer than 911 characters, whereas in the article below, the symbol 1823 is referenced

Problem

When you run the Microsoft Visual Basic for Applications (VBA) macro to transfer data from a VBA array that contains rows of data into a range of cells in a Microsoft Excel worksheet, the data may be truncated (disabled).

Note. In Microsoft Office Excel 2003 and later versions of Excel, you may receive the following error message when you run the VBA macro in the Visual Basic Editor: Run-time error '1004'

CAUSE

This issue may occur when one of the following conditions is true:

• In Excel 2007, a VBA array is longer than 8203 characters long .
• In Excel 2003 and earlier versions of Excel, a VBA array is longer than 1823 characters