100% RANGE

[FIRSTROUNDKO via OfficeKB.com]

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

 

[Joel]

Try something like this

Set DataRange = Range("A110")
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]

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

Try something like this

Set DataRange = Range("A110")
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

 

[FIRSTROUNDKO via OfficeKB.com]

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

Try something like this

Set DataRange = Range("A110")
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