The object invoked has disconnected from its clients

[Michelle]

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

 

[Michelle]

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

 

[Rick Rothstein]

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 Rothstein]

You should be able to use this code to do what you want though...

vArr = Split(BigString, ".")
Set trange = Sheets("Sheet1").Cells(2, 2)
For X = UBound(vArr) To 0 Step -1
trange.Offset(UBound(vArr) - X).Value = vArr(X)
Next

 

[Michelle]

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]

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.

 

[Michelle]

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]

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