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

**jasond1992** · 12-07-2015, 01:32 AM

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.

**jasond1992** · 12-07-2015, 02:49 AM

..........

**Keebellah** · 12-07-2015, 03:01 AM

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?

**jasond1992** · 12-07-2015, 03:09 AM

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

**Keebellah** · 12-07-2015, 03:14 AM

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

**jasond1992** · 12-07-2015, 03:19 AM

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

**Keebellah** · 12-07-2015, 03:23 AM

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

**jasond1992** · 12-07-2015, 03:38 AM

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..

**Keebellah** · 12-07-2015, 03:43 AM

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

**jasond1992** · 12-07-2015, 03:50 AM

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.

**Keebellah** · 12-07-2015, 04:54 AM

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

**Keebellah** · 12-07-2015, 05:00 AM

Another idea, place the formulas in the top row:

**jasond1992** · 12-07-2015, 05:11 AM

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

Please Login or Register  to view this content.

**Keebellah** · 12-07-2015, 05:25 AM

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?

**jasond1992** · 12-07-2015, 09:09 PM

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

**jasond1992** · 12-07-2015, 09:37 PM

Originally Posted by Keebellah

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

**Keebellah** · 12-08-2015, 01:57 AM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Insert Sumif formula but range may change

[SOLVED] Need a code or formula that will automatically change the named range in my formulas

Macro to insert row(s) and copy the formula and value of cell(s) automatically

Insert row after date change

Need to create a macro to insert row after a value change in a column

Macro to insert this formula automatically on start up

macro to Insert rows at every change in a column

Bookmarks

Bookmarks

Posting Permissions