Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Location
    Lancaster, Pennsylvania
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel VBA, #NAME and Type Mismatch errors (Excel 2003, VBA)

    Hi all,

    I have a VBA sub that stores the contents of a worksheet in an array, and then later, dumps the contents of the array into another worksheet. I'm running into a problem with this subprocedure because the data in the worksheet was taken from another source, and contains characters that Excel doesn't understand (i.e. the cells begin with "=" or with "-", and the cells are not formulas). The cells are displayed in Excel as "#NAME". But, when I activate the cell, I can still she the contents of the cell, which Excel has stored in the formula.

    My problem is, when I am storing the value or formula of the cell into the array, I get a type mismatch error unless I use the variant type for my array. Then, if I did use the variant type for my array, when I try to dump the contents of my array into another worksheet, the formula for the cell is missing. Now, the only thing stored in the new cell is "#NAME".

    Has anyone else run into this problem? Are there any simple solutions that do not involve changing the data in the cell so that Excel does not recognize it as a formula?

    Thanks for your help!

    AJ

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA, #NAME and Type Mismatch errors (Excel 2003, VBA)

    Without seeing your worksheet and VBA procedure, I can think of two possible fixes:

    1- Before storing the data into the worksheet, format the cells as Text. Then texting beginning with "=" or "-" will just be stored as text not as formulas.

    2- When you retrieve the data from the cell, use the .Formula property instead of the .Value (default) property. That will retrieve the formula string, not the result of the formula.
    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA, #NAME and Type Mismatch errors (Excel 2003, VBA)

    A few ideas come to mind, depending on how you read and write the array. If writing back strCell one cell at a time, something like:
    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black>If Left(strCell, 1) = "=" Or Left(strCell, 1) = "-" Then _
    strCell = Chr(39) & strCell <font color=448800>'Chr(39) is a ' character</font color=448800>
    </font color=black></code></div hiblock>This would ensure the new cell contents would be inserted as literal text. If there are many instances of "rogue" first characters, a Select Case construct might be appropriate.

    Alan

  4. #4
    New Lounger
    Join Date
    Apr 2004
    Location
    Lancaster, Pennsylvania
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA, #NAME and Type Mismatch errors (Excel 2003, VBA)

    Thanks Legare and Alan,

    I think I understand what I need to do now... It will just take a little more rigor than my original code. I appreciate your answers, that's exactly what I needed to know.

    Best Regards,

    AJ

Posting Permissions

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