Update formula when inserting/deleting coulumns

[Konta]

i've created 2 macro buttons to insert/remove columns

i want the formula under HOURS to update to include the newly added columns
for the minimum 3 periods the formula looks like this

=SUM((C3-B3);(E3-D3);(G3-F3))

if i press the button to add a period the formula doesn't update to include (I3-H3) period and so on

after some searches i found 2 suggestions

=SUM(INDIRECT("b4:"&ADDRESS(ROW();COLUMN()-1)))
and
=SUM((INDEX(5:5;2):INDEX(5:5;COLUMN()-1)))

but this only sums everything up to COLUMN()-1 and i can't figure out how can i modify them to suit my needs.
i want the period from "from" to "to" to be calculated for each interval and summed so i would get a total of hours worked per day




as a side request. for my VBA code after the creation of 2 columns is there a simpler way to fill them with "FROM" and "TO" than i come up with? (which is basic text inserting but get the job done)

Please Login or Register  to view this content.

thanks

[Kaper]

As for the second part:

Please Login or Register  to view this content.

or even shorter:

Please Login or Register  to view this content.

Please note how using code tags improves readability of the code and edit your above post to comply wit rule 2 of https://www.excelforum.com/forum-rul...rum-rules.html

[Kaper]

Perfect, Thanks for editing your post.
I'd recommend such formula for your case:

Please Login or Register  to view this content.

But to make it easy to apply automatically, Id use its R1C1 notation:

Please Login or Register  to view this content.

So the code for insert (BTW, I'd recommend to change the name just to avoid risk of usage problems Insert is not a restricted word in VBA, but better safe than sorry):

Please Login or Register  to view this content.

[Konta]

wow, that was fast


i tried the first formula and discovered that i need to manually click update formula to include adjacent cells when inserting columns and filling them up with data.
tried the second one but i get an error stating there is a problem with the formula. don't know why since is the same as the vba one
the vba code works flawlessly but now i need to find out what all that does given my vba beginner level

i'll mark the thread as solved but if there is any chance that this can be done without VBA please post it here

thanks for your fast reply

[Kaper]

Well, I don't like it, as it is a volatile formula, but it works

Please Login or Register  to view this content.

It was already long, so instead of testing separately isodd() (for odd 1 for even 0) and -iseven (for even -1 and for odd 0), I used isodd()*2-1 so for odd result is 1 and for even -1

[Konta]

much appreciated

the only thing left now is to find out the logic behind the formulas you provided.
although the vba one is easier for me to understand certain commands i need to find out why they were used

thanks again
and be safe in this troubled times

[Kaper]

As for formulas logic - you have to start reading from the innermost one. The useful tool oi+is Formulas-> evaluate and step by step evaluation.
so for instance with the above one we start with
OFFSET($B3,0,0,1,COLUMN()-2)
which returns (provided the formula is in column H
B3:G3
then we take

ISODD(COLUMN(OFFSET($B3,0,0,1,COLUMN()-2))
which is
{false/true/false/true/false/true}
then
2*ISODD(COLUMN(OFFSET($B3,0,0,1,COLUMN()-2)))-1
is
{-1/1/-1/1/-1/1}
and then it is multiplied by
INDIRECT(ADDRESS(ROW(),2)&":"&ADDRESS(ROW(),COLUMN()-1))
so by
B3:G3 content and results in
{-B3/C3/-D3/E3/-F3/G3}

Now we use SUMPRODUCT (could use SUM but committed as array formula by Ctrl+Shift+Enter)

to sum the content of the cells {-B3/C3/-D3/E3/-F3/G3} or after rearranging: … C3-B3 + E3-D3 + G3-F3

Best,

Kaper

[Konta]

while braking down your vba code found a yt video that's doing something similar but in a different way.

Please Login or Register  to view this content.

tried to apply it to your code but gives a mistyped error
your

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

basically i wanted to use the "hours" column size as reference not column "a" but for some reason excel does not allow it, although for the range(range.. code it works

strangely for me tried to replicate it in a blank document with a1=1, a2=2 etc as header and it works. but the moment a1= anything than 1, a2= anything but 2 gives an error
could you please explain why this happens, and why in your code isn't possible to use "hours" column as reference?
also is there any difference in functionality between the Range(Range....code and Resize(Cells(Rows... code?

thanks

[Kaper]

This End(xlDown) works as pressing Ctrl+downArrow

And the code you posted worked well when I copied it into your sample file
of course there had to be HOUR in some cell in row 2.

Test the attachment.

[Konta]

hope this makes me better understood

i was trying to replace "A" in your code with "HOURS" for the first file so the reference column is not "A" anymore but instead the column that has "HOURS" as header

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

but gives an error



this works for the second file, and for the one you posted but under certain circumstances

for the file you posted see pics related

Attachment 668567
Attachment 668568
Attachment 668569
Attachment 668570
Attachment 668571


everything that you provided works flawlessly, but just stumbled over this issues and wander if they can be easily fixed and how.
i'm just trying to lean some excel stuff while i'm being locked inside my home. hope i'm not stressing you out.


thanks

[Kaper]

As for:

Please Login or Register  to view this content.

try:

Please Login or Register  to view this content.

Sorry for late answer, but I'm pretty busy (my daily work tasks take longer time) while working from home :-(