Copy cell with more than 921 charcters

Solved/Closed
deepindersingh0142
Posts
3
Registration date
Friday December 2, 2011
Status
Member
Last seen
December 5, 2011
- Dec 5, 2011 at 10:44 AM
deepindersingh0142
Posts
3
Registration date
Friday December 2, 2011
Status
Member
Last seen
December 5, 2011
- Dec 5, 2011 at 11:57 AM
Hello,

I wrote a vb code to copy all rows where column B contains "RW". The code first deletes any data that the sheet might already have. Following is the code:

Sub Renewals_Click()
Set e = Sheets("Renewals")
Set k = Sheets("To be Engaged")

Dim d
Dim m
d = 4
m = 2

Do Until IsEmpty(e.Range("B" & d))

e.Rows(d).Value = ""
d = d + 1
Loop

d = 3
Do Until IsEmpty(k.Range("B" & m))

If k.Range("B" & m) = "RW" Then
d = d + 1
e.Rows(d).Value = k.Rows(m).Value
End If
m = m + 1
Loop
End Sub

I hit error 1004 when this code tries to copy a cell that contains more than 921 characters. Does anyone has a solution to overcome this error?!?!


Thanks in advance!

3 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Dec 5, 2011 at 11:30 AM
I am not sure about the character limit. However how about trying to truely copying and paste instead of
e.Rows(d).Value = k.Rows(m).Value


e.Rows(d).copy
k.cells(m,1).pastespecial
0
deepindersingh0142
Posts
3
Registration date
Friday December 2, 2011
Status
Member
Last seen
December 5, 2011

Dec 5, 2011 at 11:42 AM
I am not sure how .copy works but when I replaced that code with your lines it left the entire sheet blank and didnt copy anything!

Ofcourse it didnt throw the error 1004 which I was earlier getting from my code. But my code crashed at the cell that had more than 921 characters!!

Thanks for your help again!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Dec 5, 2011 at 11:44 AM
oops
it was supposed to be

k.Rows(m).copy
e.cells(d,1).pastespecial

My bad..
0
deepindersingh0142
Posts
3
Registration date
Friday December 2, 2011
Status
Member
Last seen
December 5, 2011

Dec 5, 2011 at 11:57 AM
Super... that worked perfect!

Many thanks!! :)
0