+ Reply to Thread
Results 1 to 14 of 14

Moving a range one column to the right without losing the cell references

  1. #1
    Registered User
    Join Date
    10-07-2013
    Location
    Exeter, New Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    48

    Moving a range one column to the right without losing the cell references

    Hello,

    I really tried to figure this one out on my own by looking at other posts and experimenting, but I'm stumped.

    I have a named range (let's call it "PatRange") where 1) the first column of data refers to a different line (let's call it "Separate_Line") on the same page, with a cell that is added every year through a fill right piece of code, and 2) the other columns refer to the first column AND to an unnamed table on another sheet that never moves. I would like:

    1) for "PatRange" to move over by one column every new year
    2) for the first column to refer to the newly added cell on "Separate_Line"
    and 3) for the other columns to refer to that static table.

    Extra, perhaps superfluous information:
    1) "PatRange" uses the table on the other sheet by multiplying the first column of "PatRange" by a cell in that table.
    2) The code I use to fill right on "Separate_Line" is:

    Please Login or Register  to view this content.
    3) The values in the unnamed table will be updated annually with hard-coded values, even though they don't move

    I've found code that moves the range, but does not move the actual values along with the range. I've also read in a few places that I shouldn't use "select," which that code did.

    I've attached a sample of what I would like, including 2 iterations. I hope I have been clear; if you have any suggestions for how I can be a better question poster, please let me know!

    Year 1
    2013 2014 2015 2016 2017
    20

    "PatRange"
    20 20 40 60 80
    20 40 60 80 100
    20 60 80 100 120
    20 80 100 120 140
    20 100 120 140 160
    20 120 140 160 180

    Year 2
    2013 2014 2015 2016 2017 2018
    20 25
    "PatRange" moved one column to the right
    25 25 50 75 100
    25 50 75 100 125
    25 75 100 125 150
    25 100 125 150 175
    25 125 150 175 200
    25 150 175 200 225

    Year 3
    2013 2014 2015 2016 2017 2018 2019
    20 25 29
    "PatRange" moved yet another column to the right
    29 29 58 87 116
    29 58 87 116 145
    29 87 116 145 174
    29 116 145 174 203
    29 145 174 203 232
    29 174 203 232 261


    This table is on a different sheet, and the cells don't move
    1 2 3 4
    2 3 4 5
    3 4 5 6
    4 5 6 7
    5 6 7 8
    6 7 8 9


    Thank you in advance,
    Pat
    Attached Files Attached Files
    Last edited by phook01; 10-11-2013 at 09:13 AM.

  2. #2
    Registered User
    Join Date
    10-07-2013
    Location
    Exeter, New Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Moving a range one column to the right without losing the cell references

    Bump no response

  3. #3
    Registered User
    Join Date
    10-07-2013
    Location
    Exeter, New Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Moving a range one column to the right without losing the cell references

    Bump no response

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Moving a range one column to the right without losing the cell references

    Hi Pat

    I believe the reason you're no getting any traffic is because the Sample File you attached does not reflect your Actual File.

    Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

    Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet that demonstrates what you wish the output to be.

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the "Add Files"... button to locate your file for uploading.
    6. This will open a new window File Upload...Click "Select Files"
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the "Upload Files" button and wait until the file has uploaded.
    10. Click the "Done" Button.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Registered User
    Join Date
    10-07-2013
    Location
    Exeter, New Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Moving a range one column to the right without losing the cell references

    John,

    Thanks for shedding light on the situation.

    I've uploaded a new, realistic workbook, and created "DESIRED RESULTS" worksheets that show what I want. I don't have code to show for the table-moving piece because I don't know how to start (and am new at this).

    I would like to run “Automate Annual Converter and Hist Proj Prices” and have the following happen:
    “Annual Converter”
    --The formulas in rows 2-9 fill right
    "Hist. & Proj. Prices – Chart”
    --The numbers in the first 10 rows of Column AO fill to the right into Column AP
    --The three tables in AO12:BD31 move, the ranges move with them, and the formulas refer to the newly created numbers in AP

    So far, I think I have the macro figured out for the first 2 items on that list (though any critique of that would be helpful). The missing item is the third, which is moving the table range, the content of the table, and having the formulas update.

    A bonus (but not necessary for the solution) would be to figure out how to move the borders along with the information.

    Thank you in advance,
    Patrick

    Test3.xlsm

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Moving a range one column to the right without losing the cell references

    Hi Pat

    You indicate
    I have the macro figured out for the first 2 items on that list
    I see the Code for the First Item on the List
    The formulas in rows 2-9 fill right
    I don't see the Code for the Second Item on the List...do you have it?
    The numbers in the first 10 rows of Column AO fill to the right into Column AP

  7. #7
    Registered User
    Join Date
    10-07-2013
    Location
    Exeter, New Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Moving a range one column to the right without losing the cell references

    This was located under "This Workbook." Which brings up another noob question: where should I put the macros, in the individual sheets or in "this workbook?"



    Please Login or Register  to view this content.
    Specifically, these lines of code move the first 10 rows of column ao:
    Please Login or Register  to view this content.

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Moving a range one column to the right without losing the cell references

    Hi Pat

    I found it...sorry about that. The Code should be placed in a General Module but we'll address that later.

  9. #9
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Moving a range one column to the right without losing the cell references

    Hi Pat

    Create a Named Range (I called it "OtherStuff") referring to all the Cells in the three tables in "Hist. & Proj. Prices - Chart".

    Put this Code in a General Module
    Please Login or Register  to view this content.
    After you've run your 2 other procedures run this Code.

    Let me know of issues...

  10. #10
    Registered User
    Join Date
    10-07-2013
    Location
    Exeter, New Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Moving a range one column to the right without losing the cell references

    Hello,

    I just got back from a business trip, and was excited to check this macro. Thank you for putting in the work!

    It is almost perfect, but there is an issue with the formulas in the three tables. Starting from the second column of each table, the formulas refer to columns D through R of the table in "Price Proj. % Input." When moving the range using your macro, these formulas should still refer to those same columns (D-R). As it is now, the macro you provided me moves those formulas to reference columns E-S (and then F-T, G-U, etc., as the code is run). Is there any way to fix this without having to go through all the formulas and adding absolute cell references?

    Thanks again,
    Pat

  11. #11
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Moving a range one column to the right without losing the cell references

    Hi Pat

    Can't get back to this until late Sunday...have Family Matters to attend to...

  12. #12
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Moving a range one column to the right without losing the cell references

    Hi Pat

    I looked at this and I believe the only solution is
    to go through all the formulas and adding absolute cell references

  13. #13
    Registered User
    Join Date
    10-07-2013
    Location
    Exeter, New Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Moving a range one column to the right without losing the cell references

    I was hoping for a magic VBA bullet, but no problem! I'm grateful for all the work you've done, and the fact that you checked back.

    It was actually a pretty quick fix once I realized that I was back in my comfort zone of function manipulation. I just used replace all to take the last bit of the sheet reference, "sheet3!", and turn it into "sheet3!$." This worked, since I was just creating an absolute reference for the column, not for the row.

    Thank you again,
    Pat

  14. #14
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Moving a range one column to the right without losing the cell references

    Hi Pat

    Glad you got it sorted.

    You're welcome...glad I could help. Thanks for the Rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copying moving range based on moving cell reff.
    By Varmark in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-19-2011, 04:47 AM
  2. Deleting rows w/out losing references
    By \/\/avefor/\/\ in forum Excel General
    Replies: 8
    Last Post: 09-25-2008, 12:28 AM
  3. Cut and Paste cells without moving cell references
    By mikeyfear in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2008, 06:31 AM
  4. Replies: 6
    Last Post: 12-02-2007, 04:03 AM
  5. [SOLVED] How to rename references from range names to cell references
    By Abbas in forum Excel General
    Replies: 1
    Last Post: 05-24-2006, 01:25 PM

Tags for this Thread

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