Rounding and Solver constraint

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to add a constraint that will round the Changed cell to the nearest 5?
Thanks
AJ
 
AJ

you can use code, something like this:

'===================================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng, cl
Set rng = [a:b] 'change with the desired range to be
affected
If Intersect(Target, rng) Is Nothing Then Exit Sub
For Each cl In Target
If cl.Value <> "" Then
cl.Value = Application.WorksheetFunction.Ceiling
(cl.Value, 5)
End If
Next cl
End Sub
'===================================================

This should be pasted in the View Code area of the sheet
where you want the constraint...right click on the sheet
tan, select view code and paste the code above in the
white area.

the code will change any cell that changed on columns a
and b, it will replace the value to the next 5 up.

to change the range change the part where it says:

set rng = [a:b] <--- change that as needed...

Cheers
Juan
 
Make the 'by changing cell' an integer and then in another cell enter
the formula ={by changing cell} * 5

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Have you tested this with Solver?

AFAIK, Solver fails when anything other than it is responsible for a
change to the worksheet.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

AJ

you can use code, something like this:

'===================================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng, cl
Set rng = [a:b] 'change with the desired range to be
affected
If Intersect(Target, rng) Is Nothing Then Exit Sub
For Each cl In Target
If cl.Value <> "" Then
cl.Value = Application.WorksheetFunction.Ceiling
(cl.Value, 5)
End If
Next cl
End Sub
'===================================================

This should be pasted in the View Code area of the sheet
where you want the constraint...right click on the sheet
tan, select view code and paste the code above in the
white area.

the code will change any cell that changed on columns a
and b, it will replace the value to the next 5 up.

to change the range change the part where it says:

set rng = [a:b] <--- change that as needed...

Cheers
Juan
-----Original Message-----
Is it possible to add a constraint that will round the Changed cell to the nearest 5?
Thanks
AJ
.
Click to expand...
Click to expand...
 
Back
Top