Split does not have that kind of limit. You can see this with the following
macro which contains 2 substrings of 500 characters each joined by a dot;
both substrings are placed into the worksheet as expected (at least they do
in my XL2003 workbook)...

Sub Test()

Dim tRange As Range
Dim tCell As Range
Dim vArr As Variant
Dim BigString As Variant

BigString = String(500, "X") & "." & String(500, "Z")

vArr = Split(BigString, ".")
Set tRange = Sheets("Sheet1").Cells(2, 2).Resize(UBound(vArr) + 1, 1)
For Each tCell In tRange
tCell = vArr(tCell.Row - 2)
Next tCell

End Sub

By the way... note that I removed the LBound(vArr) function call from your
Set statement... the lower bound of all arrays produced by the Split
function is **always** 0 (no matter what your Option Base is set to).

--
Rick (MVP - Excel)


"Michelle" wrote in message
...
I have posted this in a different form too - sorry if it's repetitive

I am using a variant which is converted to an array, using the Split
function and then each element is put into a cell

But it doesn't like having more that 255 characters in any element - I get
a #VALUE! error in the cell, and no more data in subsequent elements

Can I make them bigger in any way? I assume that it's the SPLIT function,
is there an alternamtive?

Here's the relevant code
===
dim vArr as Variant

vArr = Split(BigString, ".")
Set tRange = Sheets("Monologue").Cells(2, 2).Resize(UBound(vArr) -
LBound(vArr) + 1, 1)
'trange2 = Sheets("Monologue").Cells(2, 2).Resize(UBound(vArr) -
LBound(vArr) + 1, 1).Address
For Each tCell In tRange
tCell = vArr(tCell.Row - 2)
Next tCell
===

thanks

M