Thanks but it didn't work. I also should have mentioned that we are not
working with formulas as I changed currencies by paste special multiply and
then formated cells to have 0 decimals.

If you care to write one that can start from the very beginning in one step
with a bunch of text thrown into the tables, that would be great.

an example: I need to change $4699CAD into $3099Euro using the conversion
rate of 0.66. A macro that could easily adapt to different currencies would
be great.



"Gord Dibben" wrote:

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