Yes, your code does work fine (as does mine), so it would seem you are
right... your error must lie elsewhere.

--
Rick (MVP - Excel)


"Michelle" wrote in message
...
Here's the lop I used, but I think the problem is elsewhere. The loop runs
fine, there are no errors now, but there is a #VALUE error when I look at
the spreadsheet,and then no more data. Here's the loop anyway:
===
Set tRange = Sheets("Monologue").Cells(2, 2).Resize(UBound(vArr) -
LBound(vArr) + 1, 1)
For Each tCell In tRange
tCell = vArr(tCell.Row - 2)
Next tCell
===
Thanks#

M

"Rick Rothstein" wrote in message
...
Did you try the loop I posted or a different one? It is always a good
idea to post the code you say isn't working so we can see what you did
and be able to make comments on it.

--
Rick (MVP - Excel)


"Michelle" wrote in message
...
Thanks for answering so quickly

I've replaced the transpose function now with a loop, and there is still
a problem. I think it may be with the SPLIT function. I'm getting a
#VALUE! error in the element with more than 255 characters now and then
no data in subsequent elements. It works fine with smaller chunks of
data.

M


"Rick Rothstein" wrote in message
...
Since Transpose is a worksheet function that is made available to the
VB via the WorksheetFunction property of the Application object, and
since I'm guessing you are not using XL2007, then my guess is it is
bound to the 256 column limitation of your copy of Excel (remember,
Transpose moves columns to rows or rows to columns, so the 256 column
limit has to come into play somewhere).

--
Rick (MVP - Excel)


"Michelle" wrote in message
...
On investigation, It seems that Application.transpose doesn't like any
array elements that are over 255 characters long - is there a way
around that?

M


"Michelle" wrote in message
...
I am splitting text into chunks separated by full-stops, then
transposing the array into a range

my code looks something like this:

vArr = Split(BigString, ".")
If IsArray(vArr) Then
Set tRange = Sheets("Monologue").Cells(2,
2).Resize(UBound(vArr) - LBound(vArr) + 1, 1)
tRange.Value = Application.Transpose(vArr) '*** this is the
line with the problem!
Else
Sheets("Monologue").Cells(2, 2).Value = vArr
End If


I am fiddling with code kindly provided by JE McGimpsey - Thanks JE -
his worked fine but I am now getting this error

Run-Time error '-2147417848(80010108)':
Automation error
The object invoked has disconnected from its clients.

Anyone know what might be going on?

Thanks

M