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

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

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

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

--
Rick (MVP - Excel)


"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

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

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

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