Very long List of currencies (eg EDM,EAT etc have to be changed to EUR.how can a macro be written to do this?

Martin

Do the following:

1. Select the range of currencies to be changed to "EUR"

2. Run the following macro (maybe attached to a button)

Sub ReplaceCurrency()

Selection.Replace What:="*", Replacement:="EUR", LookAt:=xlPart, SearchOrder:=xlByRows

End Sub

Hope this helps

Carlos

Hi Carlos,
Ran the macro and it replaced all the cells with EUR.
I want to replace only the items in column 9 which begin with the letter E
Many thanks
Regards
Martino

Martin

To do that, in the above code add an "E" to the what line as seen below

What:="E*"

This will only replace names starting with E

Carlos

I entered the code below but nothing happened at all
Sub ReplaceCurrency()

Selection.Replace What:="E*", Replacement:="EUR", LookAt:=xlPart, SearchOrder:=xlByRows

End Sub

Martin

You need to attach the macro to a button on a toolbar or on the Worksheet.

Then you need to manually Select the range of names that need to change and click the button.

To Automatically select the names insert a line, just after the Sub line, to select the required range eg Range ("K3:K100").Select

Carlos

thanks Carlos
That works much better. However it is now changing SEK into SEUR. I want only currencies begunning with E to be changed to EUR. Is there a way to achieve this?
Regards
Martin

Martin

When I tested the code I gave you I didn't test for that. The replace function replaces ALL instances of the text.

To get around that you need to check if the first letter if the text is an "E" and only then use replace. The code below should resolve the problem:

Sub ReplaceCurrency()

Dim iColumn As Integer 'The Numeric value of the column of the Active Cell
Dim NumRows As Integer 'The number of rows with data
Dim Counter As Integer 'The Counter used to indicate the new cell being checked
Dim MyCurrency As String 'The Currency value being checked

'Select the first cell of the currency column

iColumn = ActiveCell.Column

NumRows = ActiveCell.CurrentRegion.Rows.Count

For Counter = 2 To NumRows

MyCurrency = Cells(Counter, iColumn).Value

If Left(MyCurrency, 1) = "E" Then

Cells(Counter, iColumn).Select
Selection.Replace What:="*", Replacement:="EUR", LookAt:=xlPart, SearchOrder:=xlByRows

End If

Next Counter

End Sub

Carlos

Thanks Carlos that's perfect!