Possibly the problem has already manifested by the time that code is
executed:

http://support.microsoft.com/kb/211878/en-us
XL2000: Linked Formula May Return a Maximum of 255 Characters

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
Cell formula length limit is 1024 though can be a bit less. But the .Value
limit is 32K for text strings (though not displayed).

Try changing

.Formula = Range("A1:Z1000").Value

to

.Value = Range("A1:Z1000").Value

In the sample you posted I think you are missing an apostrophe before the
!
in the formula. string

Regards,
Peter T

"Noah" wrote in message
...
I am using the macro1 to grab values from a closed workbook. Column Z
contains text cells, many of which have text strings of approximately
1000
characters...so I am running into problems with the 255 character limit.
Is
there a way to modify the "Looper" macro at
http://support.microsoft.com/kb/213841/ or some different macro in order
to
pull in the full text length of those cells in column z? Thanks! -Noah

Sub Macro1()
With Range("A1:Z1000")
.FormulaR1C1 = "='[Book1.xls]Sheet1!RC"
.Formula = Range("A1:Z1000").Value
End With