Or perhaps this revised edition which will change just formulas only.

Sub RoundDownRange()
Dim OldVal As Object
Dim NewVal As String
For Each OldVal In Selection
If OldVal.HasFormula = True Then
If Not OldVal.Formula Like "=ROUND(*" Then
NewVal = OldVal.Formula
If Left(NewVal, 1) = "=" Then
NewVal = Right(NewVal, Len(NewVal) - 1)
End If
NewVal = "=ROUND(" & NewVal & ",-1)-1"
OldVal.Formula = NewVal
End If
End If
Next OldVal
End Sub


Gord Dibben MS Excel MVP

On Wed, 13 Dec 2006 16:13:27 +1100, "macropod" wrote:

Hi mateo,

Had you mentioned there being an issue with text & values being interspersed, I might have suggested using:

Sub RoundDownRange()
Dim OldVal As Object
Dim NewVal As String
For Each OldVal In Selection
If Application.WorksheetFunction.IsNumber(OldVal.Valu e) Then
NewVal = OldVal.Formula
If Left(NewVal, 1) = "=" Then
NewVal = Right(NewVal, Len(NewVal) - 1)
End If
NewVal = "=ROUND(" & NewVal & ",-1)-1"
OldVal.Formula = NewVal
End If
Next OldVal
End Sub

Although the code does what you asked for, the results are a bit odd (ie -1) if the starting value is less than 5.

Cheers