Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code to Round Range & Not Exceed 100
I've got a file with roughly 100 rows of data in about 15 columns. For each row, I've got two ranges (one 10 columns) and another (5) columns that have %'s totaling 100% for each of the ranges.
I know I can use the round function to ROUND the range, but then I occasionally get a number when I sum that range greater than 100 or less than 100. Is there is a VBA code to check each of the ranges, round the values to 0 decimal places, but not exceed 100. I'm not against adding/subtracting from the largest value in that range. Then when done, move on to the next row through the bottom of the sheet. Thanks in advance for your help with this. Appreciate it. ConorFinnegan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code to Round Range & Not Exceed 100
Hi Connor,
Am Wed, 22 Apr 2015 07:28:46 -0700 (PDT) schrieb Conor Finnegan: I've got a file with roughly 100 rows of data in about 15 columns. For each row, I've got two ranges (one 10 columns) and another (5) columns that have %'s totaling 100% for each of the ranges. I know I can use the round function to ROUND the range, but then I occasionally get a number when I sum that range greater than 100 or less than 100. Is there is a VBA code to check each of the ranges, round the values to 0 decimal places, but not exceed 100. I'm not against adding/subtracting from the largest value in that range. Then when done, move on to the next row through the bottom of the sheet. format your cells custom with % without decimals 0% Then no decimals are displayed but the values and the sum are correct Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code to Round Range & Not Exceed 100
I've got a file with roughly 100 rows of data in about 15 columns.
For each row, I've got two ranges (one 10 columns) and another (5) columns that have %'s totaling 100% for each of the ranges. I know I can use the round function to ROUND the range, but then I occasionally get a number when I sum that range greater than 100 or less than 100. Is there is a VBA code to check each of the ranges, round the values to 0 decimal places, but not exceed 100. I'm not against adding/subtracting from the largest value in that range. Then when done, move on to the next row through the bottom of the sheet. Thanks in advance for your help with this. Appreciate it. ConorFinnegan Sounds to me like you're doing 'weighted' scoring. If so, please elaborate... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code to Round Range & Not Exceed 100
We have %'s that have decimal places and simply rounding sometimes leaves rows where the range doesn't equal 100% (some are less than 100 and some are more). I know I can format the cells to hide the decimals, but I need the values to be whole numbers and have a sum of 100. And in order to correct the discrepancy, I wanted to just place the difference in the largest value in the range or subtract from it, as needed.
I'm really looking for a VBA Solution to do this. Thanks again Garry and Claus for your responses. Appreciated the help. Conor |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code to Round Range & Not Exceed 100
Hi Conor,
Am Wed, 22 Apr 2015 18:08:59 -0700 (PDT) schrieb Conor Finnegan: We have %'s that have decimal places and simply rounding sometimes leaves rows where the range doesn't equal 100% (some are less than 100 and some are more). I know I can format the cells to hide the decimals, but I need the values to be whole numbers and have a sum of 100. And in order to correct the discrepancy, I wanted to just place the difference in the largest value in the range or subtract from it, as needed. try: Sub RoundNumbers() Dim LCol As Long, i As Long, j As Long Dim rng1 As Range, rng2 As Range Dim varData1 As Variant, varData2 As Variant 'Start row of the range with 10 rows Const lngStart1 = 2 'Start row of the range with 5 rows Const lngStart2 = 20 With ActiveSheet 'Last column LCol = .Cells(1, Columns.Count).End(xlToLeft).Column 'First range Set rng1 = .Range(.Cells(lngStart1, 1), .Cells(lngStart1 + 9, LCol)) 'Second range Set rng2 = .Range(.Cells(lngStart2, 1), .Cells(lngStart2 + 4, LCol)) varData1 = rng1 varData2 = rng2 For i = 1 To UBound(varData1) For j = 1 To LCol varData1(i, j) = Round(varData1(i, j), 2) Next Next For i = 1 To UBound(varData2) For j = 1 To LCol varData2(i, j) = Round(varData2(i, j), 2) Next Next rng1 = varData1 rng2 = varData2 End With End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF, but Not to Exceed | Excel Worksheet Functions | |||
Cannot Exceed | Excel Worksheet Functions | |||
Matching a value, which does not exceed Min or Max. | Excel Worksheet Functions | |||
How can I apply the ROUND function to a range of cells in a workbo | Excel Discussion (Misc queries) | |||
How do I ROUND() round off decimals of a column dataset? | Excel Worksheet Functions |