Code to calulate percent of total in column

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Hello-

A5:A14 contain number values, and A15 is the total of these numbers. In
column B, I need to figure the percent of total for the numbers in
column A. For this example, B5 would = A5/A15, B6 would = A6/A15, etc,
etc.
The amount of rows in column A will change each time I run the report
so I need the code to start in B5 and fill down to the next to last
value in column as (since the last value is the total of column A). Any
help will be appreciated.

Scott
 
The following should do the trick

dim Total as double
dim cell as range

total = range("A15").value

For each cell in range("B5:B14")
cell.value = (cell.offset(0,1).value / Total)
next cell
 
This does not look like it will handle the changes in the amount of
rows in column A though. It was A5:A15 for the example, but it might be
A5:A22 one day and A5:A37 the next....
 
This code assumes that the last populated cell in column A is the Total (A15
in your example)...

sub Whatever()
dim rngAllValues as Range
dim rngCurrent as Range
dim wks as worksheet
dim dblTotal as Double

set wks = sheets("Sheet1")
with wks
set rngallvalue = .range(.range("A5"), _
.cells(rows.count, "A").end(xlup).offset(-1, 0))
end with
dblTotal = application.sum(rngallvalues)
for each rngcurrent in rngallvalues
rngcurrent.offset(0,1).value = rngcurrent.value / dbltotal
next rngcurrent
end sub
 
Jim-

I am getting an error in the following line:

dblTotal = Application.Sum(rngAllValues)

Any ideas?
 
add an s to this variable

set rngallvalue =
so it look like this
set rngallvalues =
 
Back
Top