+ Reply to Thread
Results 1 to 17 of 17

Automatically change the range of the Macro Formula when i insert column or row ??

  1. #1
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Automatically change the range of the Macro Formula when i insert column or row ??

    Automatically change the range of the Macro Formula when i insert column or row

    Hello excel forum.. is it possible to automatically change the range of the formula in my macro code ?
    For example: i create a code in my sheet 1. get the sum in H1:H10 the result is in H11.. and when i try to insert a row or column Between my the range i set
    it will automatically change the range ?

    like when you create a formula not using the macro.. when you add other column or row in the range of the formula. the formula will adjust..
    is it possible ? anybody can help me..

    sorry for my grammar if you don' understand.. i wish anybody here can help me.. Thanks GOD Bless..



    Here's the code
    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    ..........

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    Hi, your explanation is clear.
    Just a question: the formula SUM(H1:Hx) will always be in Hx+1 and there are NO more rows with values below that row? And what about F column? The same here?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  4. #4
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    yes.. like for example.. on inserting a row between the range H9:H10.. when you insert a row
    Please Login or Register  to view this content.
    this range will automatically adjust.. it will become
    Please Login or Register  to view this content.
    and for the column like for example this range.
    Please Login or Register  to view this content.
    when i insert a column to the left side of "F" range of F will become
    Please Login or Register  to view this content.
    like if you create formula in a cell without using a macro they automatically adjust the range of the formula if you insert row or column..

    i wish i explain well. THnaks GOD Bless
    Last edited by jasond1992; 12-07-2015 at 03:12 AM.

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    Place this code in the Sheet's vba
    Please Login or Register  to view this content.
    You have to always insert rows at the same time in both columns or else it won't work, unless the sum formula is not to be an the same row, then you will have to split the code for column F and for column H

  6. #6
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    what if it's not needed to input row or column... i will input row or column if i do something wrong in my data or short data information

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    Please explain. I don't understand what you mean.

  8. #8
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    uhmmm. the reason why i want that sir.. because when i finish to input all the data of my excel file and i input the formula using a macro.. i will give it to my BOSS.. and if HE will add or insert row or column the formula i created will work correctly..

    base on what you say.. you say that i need to always insert a row at the same time to work the program that you give sir.
    what if i will insert a row or column if i forgot to insert some data in the middle of my excel file sheet..

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    I never mentioned a column. The macro code just reacts to any changes you make to the values in column F or H
    IF you insert a row the formula will update.
    IF you insert a column before column J the formulas will no longer work properly because they ONLY work for columns F and H

  10. #10
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    Ahh okay sir.. hehe. sorry if i don't get it fast.. is this correct where the code i put..


    Please Login or Register  to view this content.

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    Let me see if I can explain what the problem is:

    This part of the code will ALWAYS place the formula in the last filled row of data in Column F AND Column H
    So if you insert a row in Column F or in Column H the formula will in both columns move one down and you do not need to use the other twoo routines becuase they will not do the same
    Please Login or Register  to view this content.
    The following part of code is nonsense, you check the rows H1 - H500 and place the formula in H11 ?????
    Please Login or Register  to view this content.
    The same here:
    The following part of code is nonsense, you check the rows F1 - F500 and place the formula in F11 ?????
    Please Login or Register  to view this content.
    If you use the code like this it will treat F and H differently
    Please Login or Register  to view this content.
    It all depends on what you want to do

  12. #12
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    Another idea, place the formulas in the top row:
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    i want to set the result in "H40" this code is correct ?

    Please Login or Register  to view this content.

  14. #14
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    NO it is not correct

    Please Login or Register  to view this content.
    This way always in row 40
    Did you check the file I attached?

  15. #15
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    Yes i check your file.. in your file that you sent to me.. where is the code that i need to change if i want to change the result of the sum. like i want to change the result in H30 what code i need to change...

    and where is the code that if i add row it will adjust the formula.. SOrry for the late reply sir

  16. #16
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    Quote Originally Posted by Keebellah View Post
    NO it is not correct

    Please Login or Register  to view this content.
    This way always in row 40
    Did you check the file I attached?
    I try this code but i got an error sir
    Attached Files Attached Files

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Automatically change the range of the Macro Formula when i insert column or row ??

    It's quite clear i the code.
    No code to add row, you just right click and add row, I just wrote the code to modify the formula

+ 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. [SOLVED] Insert Sumif formula but range may change
    By batchy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-07-2014, 12:36 AM
  2. [SOLVED] Need a code or formula that will automatically change the named range in my formulas
    By mo4391 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-28-2013, 11:24 AM
  3. Macro to insert row(s) and copy the formula and value of cell(s) automatically
    By jwidjaja in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-17-2013, 05:35 PM
  4. Insert row after date change
    By nana15nuna in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-24-2012, 11:43 PM
  5. Need to create a macro to insert row after a value change in a column
    By vjboaz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-01-2008, 02:04 PM
  6. Macro to insert this formula automatically on start up
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-07-2005, 06:25 PM
  7. [SOLVED] macro to Insert rows at every change in a column
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2005, 09:05 AM

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