Code to calulate percent of total in column
 

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

Code to calulate percent of total in column
 

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
--
JNW


"Scott" wrote:

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

Code to calulate percent of total in column
 

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....

JNW wrote:
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
--
JNW


"Scott" wrote:

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

Code to calulate percent of total in column
 

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

--
HTH...

Jim Thomlinson


"Scott" wrote:

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....

JNW wrote:
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
--
JNW


"Scott" wrote:

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

Code to calulate percent of total in column
 

Jim-

I am getting an error in the following line:

dblTotal = Application.Sum(rngAllValues)

Any ideas?

Jim Thomlinson wrote:
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

--
HTH...

Jim Thomlinson


"Scott" wrote:

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....

JNW wrote:
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
--
JNW


"Scott" wrote:

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

Code to calulate percent of total in column
 

add an s to this variable

set rngallvalue =
so it look like this
set rngallvalues =

--


Gary


"Scott" wrote in message
ups.com...
Jim-

I am getting an error in the following line:

dblTotal = Application.Sum(rngAllValues)

Any ideas?

Jim Thomlinson wrote:
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

--
HTH...

Jim Thomlinson


"Scott" wrote:

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....

JNW wrote:
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
--
JNW


"Scott" wrote:

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