100% RANGE
 

Hi

I have a selected range of cells which total 99.98%
I need to replace the largest value to round up to 100%
Please can anybody help me.

Regards

Firstroundko

--
Message posted via http://www.officekb.com

100% RANGE
 

Try something like this

Set DataRange = Range("A1:D10")
Total = WorksheetFunction.Sum(DataRange)
MaxVal = WorksheetFunction.Max(DataRange)
'find Max Value
Set c = DataRange.Find(what:=MaxVal, _
LookIn:=xlValues, lookat:=xlWhole)
'Assume 100% is really 1.00
c.Value = (1# - Total) + c.Value

"FIRSTROUNDKO via OfficeKB.com" wrote:

Hi

I have a selected range of cells which total 99.98%
I need to replace the largest value to round up to 100%
Please can anybody help me.

Regards

Firstroundko

--
Message posted via http://www.officekb.com

100% RANGE
 

Thanks Joel I adapted it to this

Set DataRange = Selection
total = WorksheetFunction.Sum(Selection)
MaxVal = WorksheetFunction.Max(Selection)
'find Max Value
Set c = DataRange.Find(what:=MaxVal, _
LookIn:=xlValues, lookat:=xlWhole)
c.Value = (100# - total) + c.Value


Joel wrote:
Try something like this

Set DataRange = Range("A1:D10")
Total = WorksheetFunction.Sum(DataRange)
MaxVal = WorksheetFunction.Max(DataRange)
'find Max Value
Set c = DataRange.Find(what:=MaxVal, _
LookIn:=xlValues, lookat:=xlWhole)
'Assume 100% is really 1.00
c.Value = (1# - Total) + c.Value

Hi

[quoted text clipped - 5 lines]

Firstroundko

--
Message posted via http://www.officekb.com

100% RANGE
 

Thanks Joel I adapted it to this

Set DataRange = Selection
total = WorksheetFunction.Sum(Selection)
MaxVal = WorksheetFunction.Max(Selection)
'find Max Value
Set c = DataRange.Find(what:=MaxVal, _
LookIn:=xlValues, lookat:=xlWhole)
c.Value = (100# - total) + c.Value


Joel wrote:
Try something like this

Set DataRange = Range("A1:D10")
Total = WorksheetFunction.Sum(DataRange)
MaxVal = WorksheetFunction.Max(DataRange)
'find Max Value
Set c = DataRange.Find(what:=MaxVal, _
LookIn:=xlValues, lookat:=xlWhole)
'Assume 100% is really 1.00
c.Value = (1# - Total) + c.Value

Hi

[quoted text clipped - 5 lines]

Firstroundko

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200808/1