Changing currencies and rounding to nearest 9

an excel macro

[Tourz]

Want to change a worksheet of prices over to a different currency and round to the nearest nine? (Eg. $5099US into $3879Euro using the conversion rate of 0.76.)

Try this macro in excel from the Microsoft Office online discussion group:

To process the whole worksheet, you could use something like:

Sub RoundDownSheet()
Dim OldVal As Object
Dim NewVal As String
Dim ConvRate
ConvRate = InputBox("What is the conversion rate?", "Currency Conversion", 1)
On Error GoTo Abort
For Each OldVal In ActiveSheet.UsedRange
If Application.WorksheetFunction.IsNumber(OldVal.Value) Then
NewVal = OldVal.Formula
If Left(NewVal, 1) = "=" Then
NewVal = Right(NewVal, Len(NewVal) - 1)
End If
NewVal = "=ROUND(" & NewVal & "*" & ConvRate & ",-1)-1"
OldVal.Formula = NewVal
End If
Next OldVal
Abort:
End Sub

This version includes a prompt for the currency conversion rate. If you don't want to apply the same conversion rate throughout the
worksheet, you'll need to change 'ActiveSheet.UsedRange' to 'Selection' and select the range the macro is to apply to. The new
formula retains both the original value and the conversion rate.

Cheers

--
macropod
[MVP - Microsoft Word]

[Tourz]

I guess you could also use this for nice price markups.