Code character limit with Resize or Transpose or cell?

[L. Howard]

This line works well in a macro dealing with a large amount of data to be transposed into a single cell.

So if it throws an error, then is it safe to assume the receiving cell character limit is exceeded or is it within the code as I have posted here.

..Cells(k, 1).Resize(rowsize:=UBound(arrOut) + 1) = _
WorksheetFunction.Transpose(arrOut)

In either case is there a work-around?

Thanks,
Howard

 

[GS]

Assuming this line has an object ref to a wks or rng...

.Cells(k, 1).Resize(UBound(arrOut) + 1, 1) = _
Application.Transpose(arrOut)

...you're 'good-to-go'!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[L. Howard]

Assuming this line has an object ref to a wks or rng...



.Cells(k, 1).Resize(UBound(arrOut) + 1, 1) = _

Application.Transpose(arrOut)



..you're 'good-to-go'!

Ok, I'll give that a go. Thanks Garry.

Howard

 

[L. Howard]

Assuming this line has an object ref to a wks or rng...



.Cells(k, 1).Resize(UBound(arrOut) + 1, 1) = _

Application.Transpose(arrOut)



..you're 'good-to-go'!



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Yes, it is in a

With Sheets("A Name of sheet")

and also in a

For i = LBound(myArr) To UBound(myArr)

Howard

 

[GS]

Yes, it is in a

With Sheets("A Name of sheet")

and also in a

For i = LBound(myArr) To UBound(myArr)

Howard

But your counter in the line of code is 'k' NOT 'i'!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[L. Howard]

But your counter in the line of code is 'k' NOT 'i'!!!

Does this make more sense? I know the entire macro is legit because it is from Claus. Myself and another amateur broadened the data the code was working on and then error. I ran you suggestion on a lesser amount of data and no problem, I don't have the greater data example amount to test with at present. Hoping it will work.

' Original
'.Cells(k, 1).Resize(rowsize:=UBound(arrOut) + 1) = _
WorksheetFunction.Transpose(arrOut)

.Cells(k, 1).Resize(UBound(arrOut) + 1, 1) = _
Application.Transpose(arrOut)
k = k + UBound(arrOut) + 1

' from Garry
' .Cells(k, 1).Resize(UBound(arrOut) + 1, 1) = _
' Application.Transpose(arrOut)

Howard

 

[GS]

Does this make more sense? I know the entire macro is legit because

it is from Claus. Myself and another amateur broadened the data the
code was working on and then error. I ran you suggestion on a lesser
amount of data and no problem, I don't have the greater data example
amount to test with at present. Hoping it will work.

' Original
'.Cells(k, 1).Resize(rowsize:=UBound(arrOut) + 1) = _
WorksheetFunction.Transpose(arrOut)

.Cells(k, 1).Resize(UBound(arrOut) + 1, 1) = _
Application.Transpose(arrOut)
k = k + UBound(arrOut) + 1

It looks okay to me! It appears you are transfering data from one sheet
to another, starting 1 row below existing data.

Note my use of
'Application.Transpose'
in place of
'WorksheetFunction.Transpose'
is purely for the purpose of making my code VB^ friendly. It allows me
to use Find/Replace for substituting my object var "appXL" for the ref
to "Application". I'm in the process, though, of just using "appXL"
everywhere I ref "Application" in code and so you may see that in
future postings of code snippets I copy/paste from working projects.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[L. Howard]

It looks okay to me! It appears you are transfering data from one sheet

to another, starting 1 row below existing data.



Note my use of

'Application.Transpose'

in place of

'WorksheetFunction.Transpose'

is purely for the purpose of making my code VB^ friendly. It allows me

to use Find/Replace for substituting my object var "appXL" for the ref

to "Application". I'm in the process, though, of just using "appXL"

everywhere I ref "Application" in code and so you may see that in

future postings of code snippets I copy/paste from working projects.

I did test it on the larger data amount and it too threw an error.

It must be a cell limitation. I'll investigate that.

Thanks for taking a look.

Howard

 

[GS]

I did test it on the larger data amount and it too threw an error.

It must be a cell limitation. I'll investigate that.

Thanks for taking a look.

Howard

There is a limit to the number of characters a cell can contain, but
trying to put more than the limit into a cell just results the data
being truncated (excess characters are trimmed off).

I'm guessing by "larger amount of data" you mean record count as in
size of the array containing the data. I find it easier to work with 2D
arrays when dealing with worksheet data. When I said I use that code in
my projects, I mostly use it when accessing a specific row or col of a
2D array via an inner 'Application.Index' function to specify which row
or col I want to extract. Once done I dump the data into the target
range. BOTH these functions work same as their WorksheetFunction
counterpart!

What I suspect you're trying to do is pull bits of data from a source
as one might do in a db query, and trying to parse that data to various
locations on a target sheet. How successfully this works depends
greatly on how your data is structured AND how your source/target
sheets' layout. Perhaps you can provide a sample that shows expected
results on a copy of a target sheet so we can get the before/after as
well!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion