VBA - Percentage calculation for a variable number of cells in a column

[Costa_Mukhar]

Hi,

I have a column that have different values with the column total at the end. I need a code to calculate the percentage/weight of each cell out of the column total, taking into consideration that cell numbers in the column are variable, everyday I may have different number of values/cells.

The column starts at cell L5 & I need to show the percentage of each cell starting from cell M5

Can somebody help me with the code, many thanks.

 

[jim may]

Try,,,

Sub AddPercentages()
LR = Range("L" & Rows.Count).End(xlUp).Row  ' determined the last non-blank row in your Column L
Columns("M:M").NumberFormat = "0.00%"
With Range("M5:M" & LR)
    .Formula = "=L5/Sum(L:L)"
End With
End Sub

 

[Costa_Mukhar]

thanks for your reply, but

.Formula = "=L5/Sum(L:L)"

is including the sum of the column as well.

there are other columns in the table to decide the range of the cells to include in the sum, lets say column B, which has the same number of cells column L has.

 

[Costa_Mukhar]

Hi....I fixed it by multiplying the formula by 2, and it works fine now.

.Formula = "=L5/Sum(L:L)*2"

Many thanks for your help