Part of my VBA Code is a bottleneck, looking for a way to clean it up

[soneil9]

Hey everyone,

So I am by no means a VBA coder. What I have done hear is recorded macros and then manipulated the VBA code behind them. I have a particular piece of code that is acting as a bottleneck. What I'm trying to do is generate percentages from the cumulative total of a dynamic range in a column:

Sub CumulativePercent()Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/LOOKUP(2,1/(C[-1]<>""""),C[-1])"
Range("D3").Select
Range("C2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
End Sub

Does anyone have a better way of tackling this where it's not locking up my excel memory?

Thank you,
Sean

 

[Norie]

Don't see any reason for the LOOKUP in the formula, calculating % of total should be just a simple formula - value/total value.

 

[Marcelo Branco]

Don't see any reason for the LOOKUP in the formula, calculating % of total should be just a simple formula - value/total value.

Agree 100%

M.

 

[dchaney]

Ahhh I see, I agree with that aswell

 

[soneil9]

Marcelo,

Your code is now working beautifully, even with thousands of rows. Thank you all very much for your support!

-Sean

 

[Marcelo Branco]

You are welcome. Glad to help

M.