VBA to add the contents of an adjacent variable column size

Hi Guys

What I am looking to do is add up the contents of column F as far as the entry in Column A is the same and put answer in Column I where the entries in Columns A changes. The number of rows change for each group as different Stocks are entered.
I hope this makes sense. I hate having to make up titles. I have attached a workbook.

Thanks a lot in advance.
Mark

Hi Mark,

See the attached file which erases the formulas and recreates them each time:
a. The workbook is opened.
b. The sheet is opened (i.e. activated).

ThisWorkBook module code:

Please Login or Register  to view this content.

Sheet Module code:

Please Login or Register  to view this content.

Ordinary Code module code (e.g. Module1):

Please Login or Register  to view this content.

Lewis

Hi LJMetzger

Thanks. I have a longer list here and had spent the last 20 minutes manually adding the percentages. Your VBA does it in the blink of an eye. Thank you. And in particular thank you for the opening book and sheet though I don't need them right now. I can not explain the thrill I get when this stuff works.
Thanks again.
Mark

Hi LJMetzger
I just had to add again how thoughtful it was for you to add all the comments. It is through these that I can learn. So thanks again
Mark

Hi LJMetzger
Me again. I am trying to sort these to highest first. I like the fact that in your solution you have kept the cells empty meaning they are fast to read. What I am trying is to order the lot now. I will be having a go at this but if you had a moment that would be great.
Thanks in advance.
Mark
Don' know where you are in the US but here in London the sun is shining and yesterday was the London Marathon.

Sorry forgot to pop up the sheet not that its much different.

Hi Mark,

In order to do what you want I had to:
a. Erase all your Border Lines
b. Add a temporary Helper Column 'J' to sort.
The value in column 'J' is of the form SNNNNNN.NNN%-AAA-ZZZZZZ where:
' S is 'A' if the Percentage is NEGATIVE, and 'Z' if the percentage is Positive
' NNNNNN.NNN% is 6.3 digit % value with possible leading MINUS sign (to make it 7.3 with MINUS sign)
' AAA is the value in Column 'A' (in upper case)
' ZZZZZZ is 100000 Minus Row Number (to force sort in descending order)
c. Add the Border Lines after sorting was completed.

Try the following Macro (which is a modified version of the previous Macro). The Macro is also contained in the attached file.

Please Login or Register  to view this content.

hI LJMetzger

I thought I had sent this note of thanks to you. Sorry for the delay.

You program works beautifully on the file I uploaded. I then tried it on a much larger selection. And as this was the last part of my main macro it was the first time I had attempted up to 140 stocks. Here I came across some issues where perhaps the Dim(s) had been specified differently earlier. I am not sure. I am going to go through them all again. And if I may could I ask your advice if I have a problem as I may miss when a description / Dim is allocated differently and even if I find them What will I do.

It is ridicules as I am putting bits from a range of "super cars" into one hoping to win the Indianapolis 500 or as we have here the Grand Prix.

In essence what I have is a macro to download historic prices from Yahoo, analyse them through several sieves till I have the final one you provided. This gives me a view of how stocks behaved over a reasonable period. I am sure this is available commercially but its much more interesting to build it, even if my methods are not actually me building it.

But again Thank you for your kind assistance and you comments. The comments are amazing and so useful.

All the Best
Mark

Hi Mark,

Thanks for the rep points. Feel free to ask anytime if you have a question or problem.

Lewis