Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Named Range Changes Each Time I Run Macro

    Hi XL Friends:
    I am using XL97 SR(2).
    OK. This is the 2nd time I am redoing this POST. I have removed the attachment with the Macro because I now understand that the macro is not the problem; it is immaterial, except for the fact that it is inserting SubTotals (or averages) into the WS. When it inserts 12 month subtotals, it moves all references in named ranges down 13 rows (12 + one for the Grand Total). This destroys all my charts and makes it necessary to set their ranges manually after each run.
    I guess I need to learn how to define the range for the charts within the macro after the subtotal rows have been inserted.
    Thanks
    Stephen

  2. #2
    Lounger
    Join Date
    Apr 2001
    Location
    Michigan, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Range Changes Each Time I Run Macro

    a shot in the dark:
    is the named range's references absolute?
    i.e. if go Insert>Name>Define and highlight the Range Name in question, in the 'Refers to:' box, is there a '$' before each row and column letter and number? If the reference is not absolute, it could potentially move all over the place.

    Just a thought.

    -Mark

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

    Re: Named Range Changes Each Time I Run Macro

    If you insert or delete rows, columns, or cells that cause a referenced cell to move, the reference will be adjusted even if it is absolute. You can usually get around this problem by using the Indirect function. For example, if you have a cell that contains =$A$5 and you insert a row between row 1 and 5, then the reference will be adjusted to =$A$6. However, if you have a cell that contains =Indirect("A5") and you insert the row, the cell will still contain =Indirect("A5").
    Legare Coleman

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    Michigan, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Range Changes Each Time I Run Macro

    if you can post an example of the underlying formula in the name range, someone may be able to help you brainstorm a method to rewrite the formula to keep the references in place.

    -Mark

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Range Changes Each Time I Run Macro

    Legare:
    I named my range as follows:

    SummFTEsByMonthTable =
    OFFSET(INDIRECT(summary_FTEs!$J$1),0,0,14,7)

    and in J1, I entered B260, which is the upper left hand corner of the range the Macro is pasting to and it worked great. Each time I run the code the named range remains the same. Great!!
    But, recall that the problem was with the named ranges in my charts. So, I returned to the charts and tried to set up the range in the Source "Data range:" window as =SummFTEsByMonthTable (see above). I click OK and I can see the 6 graphs in the "Source" Screen but I get the error message "Reference is not Valid" from that stupid little pia MS Help dodad. If I click "OK", it gives me a useless message and I am unable to Click on the "Series" tab to set the Chart name, the Data area, and the x-Axis labels.
    If, instead, I skip the Data Range step and go directly to the "Series" Dialog Box, click in the "Name:" box and enter
    =ChartName_AN
    where

    ChartName_AN=Indirect(summary_FTEs!$C$1) and "C260" is entered in cell C1,

    I get the same "Reference is not Valid" message.
    When I check the named range "ChartName_AN =INDIRECT(summary_FTEs!$C$1)"
    it works fine.
    So, I am back to resetting the Chart ranges manually again.

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

    Re: Named Range Changes Each Time I Run Macro

    Unfortunately, I don't know much about charts and how to set them up. Maybe someone else can help.
    Legare Coleman

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

    Re: Named Range Changes Each Time I Run Macro

    You didn't say what error you are getting and that makes it a little tough to figure out what is happening. Code running in event modules sometimes has some wierd behavior. Here are a couple of things to try:

    1- Put a worksheet object refrence on that range statement:

    <pre>Worksheets("FTE_Chart_Data").Range("A1:A1").S elect
    </pre>


    2- Take your whole routine and move it to a new procedure in a module and then call it from the button click event code.

    Depending on what your error is, one of these might clear it up.
    Legare Coleman

  8. #8
    Lounger
    Join Date
    Apr 2001
    Location
    Michigan, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Range Changes Each Time I Run Macro

    when you're entering range names in the chart "Data Range" (and others) dialog boxes, I think you have to enter the sheet name where the range is located i.e.:
    =Sheet1!ChartName_AN

    HTH

    Mark

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Range Changes Each Time I Run Macro

    Mark and Legare:
    Did not see your posts til I got home tonight. I finally solved the problem by having the macro copy the data ranges to separate WS. Then, I key my charts to the ranges on these WS.
    I did try defining the chart ranges with
    offset($B$1,260,0,13,7) and, in this case, the ranges did not move when the subTotal rows were inserted. However, after entering the range name into the Chart Source Data, Data Range, XL immediately converts the Data Range to its equivalent cell addresses ($B$260:$H$273) and the next time I run the macro the Chart Data Range shifts to ($B$273:$H$286) , eventhough the named range does not shift. I had enough of this crazziness, so I opted for the brute force solution.
    Regards
    Stephen

  10. #10
    Lounger
    Join Date
    Apr 2001
    Location
    Michigan, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Range Changes Each Time I Run Macro

    Stephen:
    Sorry I didn't pay enough attention to your offset calc. I do something similar to what your trying to do, I don't think you can enter the entire chart range in the 'Data Range' range box, you need to go to the 'Series' tab and give each element of the chart a named range (Name, Values, and Category (X) axis labels). So each data series on the chart will have two named ranges - one for the name and one for the values. plus one named range for the axis labels.

    From your offset function it looks like you have about 12 data series, so you would need about 25 named ranges. It's seems to be a Herculean task to set up the chart with all the named ranges when you're doing it, but it may be worth it if you have to update the sheet often.

    You may want to try the method with one or two data series to confirm that the method works.

    I attached a small example chart so you can see what the 'Series' tab looks like with named ranges when you edit the 'Source Data...' (It shows the workbook name as part of the named range, but I used the sheet name when editing the ranges.)
    HTH
    Mark
    Attached Files Attached Files

Posting Permissions

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