Hi All,

I hit the 255 char limit when coping cells (more than 255 chars are
truncated), but even after looking through tens of pages I couldn't
find any good answer. My hope it that someone here knows a good answer

BTW this is from my work computer, Excel 2000, but I had the same on a
Excel 2003 computer.

I have made a Excel VBA sub that merges 2 sheets in 2 books together
on a new sheet; However sheet A, containing some cells larger then 255
chars but has less rows than sheet B. Copying sheet A to C first is no
option.

my code simplified:

.....
Dim c as Range
Dim i as Interger, rw as Integer
set wsa workbook("filenameA.xls").worksheets("A")
set wsb workbook("filenameB.xls").worksheets("B")
set wsc workbook("filenameB.xls").worksheets("C")
.....
' I lookup certain values in sheet B and copying them to sheet C....
.....
' value to be looked up in sheet A is in variable i now
.......
' set range in where to find i
' rw contains correct row in worksheet c to write values
.....
Set c = .Find(i, LookIn:=xlValues)
If Not c Is Nothing Then

' trying to overcome with copying an paste special
wsa.Cells(c.row, 8).Copy
wsc.Cells(rw, 10).PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' this apparently doesn't copy more then 255 chars

' found some code op MS website,
' to be honost I am not a regular VBA programmer, don't understand the
' Dim st2() as integer very well.
' Argh! is it not working too well, getting errors on cells with more
than
' 255 chars. Others are copied.

Dim st1 as string
Dim st2() as string
Dim x as integer

st1 = wsb.cells(c.row,2).value
For x = 1 To (Int(Len(st1) / 255) + 1)
st2(x) = Mid(st1, ((x - 1) * 255) + 1, 255)
Next x

wsc.cells(rw,11) = st2
' Argh! is it not working too well, getting errors on cells with more
than
' 255 chars. Others are copied.

end if

------------