Percent increase calculations...

[Spitfire999]

Good morning to everyone...(or whatever time of day it may be)

I have been working on this code for a little while (with lots of help form NBVC and Ska67can)

I was wondering if anyone could help me with the small amount of code needed to be able to automatically calculate the percentage needed from one year to the next. I simply need it to automatically create a third column each time the copy old data button is pressed, and I need the column to have the percent increase ((current year - past year)/current year). Hopefully you can understand what I am aiming for. I have the current code below, and I will attach a compressed and stripped version of my workbook.

Thanks a bunch to everyone in advance.

Please Login or Register  to view this content.

[Spitfire999]

Anyone?

I would really appreciate it.

[NBVC]

Not exactly sure what which columns you mean by "current year" and "past year"

Try this code and see if it does what you want. You will see that after you hit the copy button, the 3rd column will be 0's, as you add figures in the Column E, the calculations will adjust.....is this right?

Please Login or Register  to view this content.

BTW: My handle is NBVC.....not MBVC...

[Spitfire999]

Sort of on the right lines, I have just attached a single spread sheet to demonstrate what the desired output should look like. When I add the current year to the history table, by running the macro, I want it to insert a column like the ones i highlighted in blue.

Hopefully that makes a bit more sense. And really sorry about the handle mishap...its all peachy now.

[NBVC]

Try this code:

Please Login or Register  to view this content.

[Spitfire999]

Wow, again worked just great...
Hopefully after this my project will be about 99% done which will make me very happy.

But just wondering, as just touch up, would you happen to know what I should put in so that

a. when the numbers are copied and pasted from the current table, that the price is formated in currency form (U.S $ with 2 decimal places)?

b. that the % increase cells are formatted in percent form.

and...

c. How if there is no value for the previous year that when calculating the increase % that it simply leaves the cell blank? (or vice versa where there is no current year price for it).

oh, and NBVC, I owe you something for all your trouble, I just have to think of what...

[NBVC]

Try this:

Please Login or Register  to view this content.

[Spitfire999]

again, wow, simply great...

I think this about finishes up this workbook, But I'm sure ill stick around and ill prolly be having some more questions in the not to distant future.

But again thank you so much to NBVC and to Ska. I really appreciate all the help that you guys gave me, and you make this forum an absolute pleasure to come to for help.

-Ben-

[NBVC]

You're quite welcome.

I am glad that we came through for you.

[ska67can]

You're welcome, and sorry I wasn't able to get back to you sooner.

ska

[NBVC]

Spitfire999,

I just cleaned up the code a bit....to make it prettier...I like to make it look flowing....still functions the same.

Please Login or Register  to view this content.

[Spitfire999]

Wow, you even cleaned up the code for me.

*feels special*

I really don't know how to thank you.

[NBVC]

Wow, you even cleaned up the code for me.

*feels special*

I really don't know how to thank you.

No worries.

It's fun playing with this stuff....when you can get it to work

[Spitfire999]

lol agreed. Hopefully ill be able to take a few VB classes this school year, so I can return the help that you guys have given me to the rest of the forum/community...

But first I have to get Access mastered..ughhh..

But thanks again, really appreciate it.