+ Reply to Thread
Results 1 to 6 of 6

Type mismatch error 13: Transpose dynamic name range when range refers to single cell

  1. #1
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Type mismatch error 13: Transpose dynamic name range when range refers to single cell

    I have a code that will transpose a single column range (which is a dynamic name range) to a 1D array. I get a type mismatch error 13 if the dynamic name range refers to a single cell range. Is this a limitation of the array or transpose function?

    Many thanks
    Asha

    N.B. The code is still WIP - I have to yet incorporate error handling.

    Please Login or Register  to view this content.
    Last edited by asha3010; 07-27-2010 at 10:59 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Type mismatch error 13: Transpose dynamic name range when range refers to single

    The fact is by declaring the Variant as an Array [c/o ()] you can't then populate with a single cell in this manner.

    Please Login or Register  to view this content.
    Worth adding that the original code isn't creating a 1d Array for vArray1/2 - these will remain 2d though transposed.
    To convert a horizontal vector to a 1d array you would transpose twice over.

  3. #3
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Type mismatch error 13: Transpose dynamic name range when range refers to single

    Hi asha3010,
    Your can count the items in the named range with
    Please Login or Register  to view this content.
    do determine what do do
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  4. #4
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Type mismatch error 13: Transpose dynamic name range when range refers to single

    Apologies for the delayed response.

    Thanks Pike & DonkeyOte for your response.

    DonkeyOte - I am trying to understand what your code does, so I have a couple of questions.

    Firstly, in the spirit that no question is a dumb question - I did not understand what you meant here

    The fact is by declaring the Variant as an Array [c/o ()] you can't then populate with a single cell in this manner.
    Secondly,

    Worth adding that the original code isn't creating a 1d Array for vArray1/2 - these will remain 2d though transposed.
    Is this because I transposed the range without first converting it to an array?

    Since the named ranges in the original code refer to a single column range (so, a vertical vector?), I added the transpose function to your code.

    Please Login or Register  to view this content.
    With this I no longer got the type mismatch error.

    Thanks in advance for your patience.
    Asha


  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Type mismatch error 13: Transpose dynamic name range when range refers to single

    Asha, correct - no question is a dumb question.

    Regards your first question concerning () in your variable declaration:

    Please Login or Register  to view this content.
    is explicitly declaring vArray1 as an Array - when you try to apply a single cell to this Array you get your Error.

    Whereas:

    Please Login or Register  to view this content.
    would not error, however, vArray would either become:

    a) a single value Variant

    b) a Variant array (>1 item)

    You can work around this issue - by testing the items in the Range as outlined by Pike or by testing the VarType of vArray once populated (ie Array or non-Array).

    However, it is of course easier if you can always ensure the output is a Variant Array irrespective of whether or not you have 1 or > 1 items.

    As illustrated you can do this using Evaluate (ie 1 to 1)


    Regards your 2nd question...

    To convert a single column range to a 1d array would warrant one transposition.
    To convert a single row range to a 1d array would warrant two transpositions.
    Apologies for not adding this to the original (untested) code.

  6. #6
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Type mismatch error 13: Transpose dynamic name range when range refers to single

    Thanks very much for your detailed explanation & patience.
    Asha

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1