1

Part of my VBA code involves copying and pasting a long string from one cell to the other. My code works fine until if there are too many characters in the source cell. After some tests, I think my code will generate an runtime error 1004 if the number of characters in Cell E16 is greater than 8000 (approximately). At this moment, I have a workaround approach which is to assign the source cell to an intermediate cell. If possible, may I know what is the cause of this issue? Do I need to split the content of my cell (E16) into more than 1 if it is really long?

This approach does not work if too many characters are in E16

Sheets(SN_Pound).Range("A10") = Sheets(SN_LV).Range("E16")

This approach works

CHR_LV_str = Sheets(SN_LV).Range("E16")
Sheets(SN_Pound).Range("A10") = CHR_LV_str
TTT
  • 4,354
  • 13
  • 73
  • 123
  • 1
    hmm .. same error for `Sheets(SN_Pound).Range("A10").Value2 = Sheets(SN_LV).Range("E16").Value2` ? The limit for "Length of formula contents 8,192 characters" but "Total number of characters that a cell can contain 32,767 characters" https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 – Slai Oct 29 '16 at 00:46

1 Answers1

3

Whatever is causing the error seems to be related to using the default property of the Range object. If the property is explicitly specified, there is no error:

With Sheets(1)
    .Range("E16").Value = String$(30000, "x")
    Debug.Print Len(.Range("E16").Value)        'Prints 30000
'    .Range("A10") = Range("E16")               'Error 1004
    .Range("A10").Value = .Range("E16").Value   'No error
    Debug.Print Len(.Range("A10").Value)        'Prints 30000
    .Range("E16").Value = String$(60000, "x")
    Debug.Print Len(.Range("E16").Value)        'Prints 32767
End With

Note that per the link that @Slai mentioned in the comments the maximum cell text length is 32767, but it simply gets truncated. There isn't an error thrown if you attempt to assign a longer string.

Comintern
  • 21,855
  • 5
  • 33
  • 80
  • Thanks so much! Actually, adding `.value` solves the problem. – TTT Oct 29 '16 at 16:49
  • I'm unsure if you meant for the `Error 1004` line to omit the `.` before `Range` and instead implicitly refer to the `ActiveSheet.Range` (it's still error 1004, if you add the period, FWIW), but it prompted me to add trailing parentheses and then compare `.Range("A10") = .Range("E16")() ` which is *Error 9 Subscript out of range*, and also to try `.Range("A10") = Range("E16")()` which is *no error*. So in addition to adding `.Value` and `[_Default]`, you can also add `()` depending upon how you reference `Range` o.O – ThunderFrame Jan 10 '17 at 10:56