Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing rounds answer to
I have a spreadsheet where each column in the spreadsheet from column 2 to
column 22 can be a record. Column 1 contains row headers. I can have from 1 to 21 records depending on the needs of the user. I need to be able to sum each row and place that value into a column 1 column past the last record column. I do this using the following code: curRow = 40 maxRow = 51 'The following looping structure sums the values from the first column to the last 'filled column, then puts the resulting value on the last column plus 1 Do While curRow <= maxRow Set sumrng = Range(Cells(curRow, 2), Cells(curRow, numrec)) SumItemWt = Application.WorksheetFunction.Sum(sumrng) Worksheets(1).Cells(curRow, numrec + 1).Value2 = SumItemWt curRow = curRow + 1 Loop When this code runs the value plced into the column numrec+1 is rounded up to the nearest whole number. The cell is set as a number with 2 decimal places. Instead of something like 4.76 I get 5.00. Why? If I place the standard cell formula manually into the spreasheet I get the correct result. Why not with this code? Any help will be greatly appreciated! Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing rounds answer to
Do you have
Dim SumItemWt as Long or Dim SumItemWt as Integer If so, that's your huckleberry. Declare it as single or double. -- Regards, Tom Ogilvy "Kevin" wrote in message ... I have a spreadsheet where each column in the spreadsheet from column 2 to column 22 can be a record. Column 1 contains row headers. I can have from 1 to 21 records depending on the needs of the user. I need to be able to sum each row and place that value into a column 1 column past the last record column. I do this using the following code: curRow = 40 maxRow = 51 'The following looping structure sums the values from the first column to the last 'filled column, then puts the resulting value on the last column plus 1 Do While curRow <= maxRow Set sumrng = Range(Cells(curRow, 2), Cells(curRow, numrec)) SumItemWt = Application.WorksheetFunction.Sum(sumrng) Worksheets(1).Cells(curRow, numrec + 1).Value2 = SumItemWt curRow = curRow + 1 Loop When this code runs the value plced into the column numrec+1 is rounded up to the nearest whole number. The cell is set as a number with 2 decimal places. Instead of something like 4.76 I get 5.00. Why? If I place the standard cell formula manually into the spreasheet I get the correct result. Why not with this code? Any help will be greatly appreciated! Kevin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing rounds answer to
Tom,
Imagine my right palm smakking my forhead and me saying ... DuuuuuH That is my problem. I an a dummy! Thanks! Kevin "Tom Ogilvy" wrote: Do you have Dim SumItemWt as Long or Dim SumItemWt as Integer If so, that's your huckleberry. Declare it as single or double. -- Regards, Tom Ogilvy "Kevin" wrote in message ... I have a spreadsheet where each column in the spreadsheet from column 2 to column 22 can be a record. Column 1 contains row headers. I can have from 1 to 21 records depending on the needs of the user. I need to be able to sum each row and place that value into a column 1 column past the last record column. I do this using the following code: curRow = 40 maxRow = 51 'The following looping structure sums the values from the first column to the last 'filled column, then puts the resulting value on the last column plus 1 Do While curRow <= maxRow Set sumrng = Range(Cells(curRow, 2), Cells(curRow, numrec)) SumItemWt = Application.WorksheetFunction.Sum(sumrng) Worksheets(1).Cells(curRow, numrec + 1).Value2 = SumItemWt curRow = curRow + 1 Loop When this code runs the value plced into the column numrec+1 is rounded up to the nearest whole number. The cell is set as a number with 2 decimal places. Instead of something like 4.76 I get 5.00. Why? If I place the standard cell formula manually into the spreasheet I get the correct result. Why not with this code? Any help will be greatly appreciated! Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 always rounds up automatically | Excel Worksheet Functions | |||
Rounds the large numeric value | Excel Discussion (Misc queries) | |||
formula needed that rounds to 99 cents | Excel Worksheet Functions | |||
Round function that rounds down if 5 | Excel Worksheet Functions | |||
How can set how a number rounds up in excel | Excel Worksheet Functions |