Can I SPLIT into bigger array elements

[Michelle]

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

 

[Rick Rothstein]

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).

 

[Leith Ross]

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

Hello Michelle,

In pre-2007 Excel versions, the limit a cell can display is 255
characters. The formula bar, however, can display up to 32K. All
characters are stored internally up to 32k. The Split function will
handle strings up to the maximum, so the problem isn't the Split
function. It merely how Excel is displaying the data.


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)

 

[JE McGimpsey]

Don't think that's the problem.

Don't remember how far in the distant past the display may have been
limited to 255 chars, but at least in XL97/98/00/01/02/v.X/03/04, the
limit is 1024.