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

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

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

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

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