Code character limit with Resize or Transpose or cell?

Discussion in 'Excel Programming' started by L. Howard, May 2, 2014.

  1. L. Howard

    L. Howard Guest

    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
     
    L. Howard, May 2, 2014
    #1
    1. Advertisements

  2. L. Howard

    GS Guest

    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
     
    GS, May 2, 2014
    #2
    1. Advertisements

  3. L. Howard

    L. Howard Guest

    On Thursday, May 1, 2014 6:30:59 PM UTC-7, GS wrote:
    > 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
    >


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

    Howard
     
    L. Howard, May 2, 2014
    #3
  4. L. Howard

    L. Howard Guest

    On Thursday, May 1, 2014 6:30:59 PM UTC-7, GS wrote:
    > 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
     
    L. Howard, May 2, 2014
    #4
  5. L. Howard

    GS Guest

    > On Thursday, May 1, 2014 6:30:59 PM UTC-7, GS wrote:
    >> 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


    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
     
    GS, May 2, 2014
    #5
  6. L. Howard

    L. Howard Guest

    On Thursday, May 1, 2014 6:57:11 PM UTC-7, GS wrote:
    > > On Thursday, May 1, 2014 6:30:59 PM UTC-7, GS wrote:

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

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


    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
     
    L. Howard, May 2, 2014
    #6
  7. L. Howard

    GS Guest

    > 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
     
    GS, May 2, 2014
    #7
  8. L. Howard

    L. Howard Guest


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


    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
     
    L. Howard, May 2, 2014
    #8
  9. L. Howard

    GS Guest

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

    >
    > 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
     
    GS, May 2, 2014
    #9
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.