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

--
macropod
[MVP - Microsoft Word]


"mateo" wrote in message ...
| This works great but is too tedious because of need to select each price
| group separately to avoid text.
|
| "macropod" wrote:
|
| Hi mateo,
|
| You can do it with a macro like the following, which will add a formula to any
| selected cells, regardless of whether they hold formulae or values. Just be
| careful not to run it against cells containing text!
|
| Sub RoundDownRange()
| Dim OldVal As Object
| Dim NewVal As String
| For Each OldVal In Selection
| NewVal = OldVal.Formula
| If Left(NewVal, 1) = "=" Then
| NewVal = Right(NewVal, Len(NewVal) - 1)
| End If
| NewVal = "=ROUND(" & NewVal & ",-1)-1"
| OldVal.Formula = NewVal
| Next OldVal
| End Sub
|
| Cheers
|
| --
| macropod
| [MVP - Microsoft Word]
|
|
| "mateo" wrote in message
| ...
| Hi, I have worksheet full of prices that I want to round to the nearest
| multiple of 10 then subtract 1 to get a nice price with 9 on the end. (having
| already multiplied the original numbers by a percent to change currencies)
|
| I know how to make $47 into $49 by using =mround(47,10)-1 but how do I
| apply it to the whole sheet? Paste special Formulas is not working for me...
|
| Thanks
|
|
|