Martin has attended:
Excel Intermediate course
Excel Advanced course
Excel VBA Intro Intermediate course
Macros
Very long List of currencies (eg EDM,EAT etc have to be changed to EUR.how can a macro be written to do this?
Replacing Multiple Currencies Macro
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
RE: Replacing Multiple Currencies Macro
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
RE: Replacing Multiple Currencies Macro
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
RE: Replacing Multiple Currencies Macro
I entered the code below but nothing happened at all
Sub ReplaceCurrency()
Selection.Replace What:="E*", Replacement:="EUR", LookAt:=xlPart, SearchOrder:=xlByRows
End Sub
RE: Replacing Multiple Currencies Macro
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
RE: Replacing Multiple Currencies Macro
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
RE: Replacing Multiple Currencies Macro
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
