| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating percent of times a value is in a column and total # | Excel Worksheet Functions | |||
| I am trying to calulate total time | Excel Worksheet Functions | |||
| code for adding a '% of total' column | Excel Programming | |||
| Help required to code a '% of total' column | Excel Programming | |||
| VB code to total up a column as the value change on another | Excel Programming | |||
Linear Mode
