Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sheet-level range name discrepancy (2000 SR1)

    Hi Folks,

    I loop through six sheets and pass each sheet to a procedure as an argument (WhichSheet in the code sample). That procedure applies some range names. Therefore, each sheet gets treated the same way. Below is a portion of that procedure:

    <pre> With WhichSheet
    .Range("Norm1", "Norm11").Name = "Y"
    .Range("CoA", "CoD").Name = "Coeffs"
    .Range("Mod1", "Mod11").Name = "Model"

    End With ' WhichSheet


    </pre>


    After this code is executed, the ranges named "Coeffs" and "Model" are properly assigned on a per-sheet basis, but the range named Y is assigned only to the last sheet.

    That is, if you pick any particular sheet (say Feature2) and choose Insert, Name, Define, the entry for "Coeffs" and "Model" each have "Feature2" listed next to them, and the sheet specification in the "Refers to:" field depends on which sheet you were on when you chose Insert, Name, Define. However, the listing for Y does not have a sheet listed next to it in the list box, and the sheet specification in the "Refers to:" field is "Feature6!..." regardless of what sheet you were on when you chose Insert, Name, Define. (see picture)

    I cannot figure out any reason behind this behavior, any ideas?!? Thanks!
    Attached Images Attached Images

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet-level range name discrepancy (2000 SR1)

    Never mind, I answered my own question. I have to modify the code as follows:

    <pre>.Range("Norm1", "Norm11").Name = WhichSheet.Name & "!Y"
    etc.
    </pre>


    The other two range names are left-overs from when I was developing and named the ranges manually, on a per-sheet basis. After deleting the "Coeffs" and "Model" range names from Insert Name, Define, these ranges also exhibited the strange naming behavior.

Posting Permissions

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