Formula with Dynamic Ranges

[soteman2005]

Hi all,

In Col E, I have a list of values which are summed on the final row. In Col H, I would like to display the percentage of the total that the row makes up i.e. if the total is 200 and E5 is 20, I would like H5 to display 10%.

The number of rows will constantly be changing so I need a way of scripting for each cell in H to be =Etotal/Ecurrentrow, but I am having trouble working out the referencing for it and can't work out how to use a dynamic range or if I should use the code for counting up to the first non-empty cell and get it to do it for every filled in cell in the column, besides the final total (i.e. 100%) one.

If anyone could provide any help, that would be amazing!

Many thanks,

[arthurbr]

Maybe =E5/(2*sum(E:E))

[Kyle123]

Maybe

As a formula:

=E5/Max(E:E)


As a VBA sub

Please Login or Register  to view this content.

[soteman2005]

Apologies, I should have been clearer. I can do it manually however I wanted to use VBA as part of a much larger script to automate a complicated process. Everything else is working fine, but it is just this section that I am having trouble with.

My current script creates a new worksheet, pastes all of the values from various worksheets into the new worksheet and arranges them in the correct order, now I am just trying to add the percentage column which doesn't feature on the various other worksheets.

As I anticipate refreshing the data regularly, I am hoping not to have to manually edit the percentage formulae each time.

Thank you,

[Kyle123]

does the above code do what you need?

[snb]

Please Login or Register  to view this content.