Convert EUR to GBP VBA Script

mattbarb

Board Regular
Joined
Mar 22, 2018
Messages
54
Office Version
  1. 365
Platform
  1. MacOS
Hi,

Im trying to create a script to change all the values on the press of a button from GBP to EUR and back if the button is presed again, can anyone help. I search online and the forum but not joy.

Many thanks
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Do you have an exchange rate stored somewhere? Where?

"Change all the values". Where are "all the values"?
 
Last edited:
Upvote 0
Hello, I literally just need to change the symbol and not the values. Sorry wasn't clear.
 
Upvote 0
Hello, I literally just need to change the symbol and not the values.
Are the symbols actually text in the cell or is the symbol there from a custom cell format?
What does =LEFT(A1,1) return if it is pointed at one of these cells? Does it return a currency symbol or a digit?

BTW, you haven't yet answered my question about where the values are.
 
Upvote 0
Hi, Sorry values are across multiple columns of the sheet. Cell does not contain the currency value.
 
Upvote 0
Hi
Try something like this

Code:
 Selection.NumberFormat = "[$€-83C]#,##0.00"
OR
Code:
 Selection.NumberFormat = "[$EUR]#,##0.00"
 
Last edited:
Upvote 0
Hi, Sorry values are across multiple columns of the sheet. Cell does not contain the currency value.
The ranges are unclear and I take the last part to mean that the currency symbol is from the cell formatting.

Anyway, see if this is any use. I have assumed that the first row is a heading row and that at least the value in row 2 of the first column to be changed is formatted as one of the desired currencies.

Test in a copy of your workbook.

Thsi is to toggle in columns E, H:J and P. Adjust code to suit your data.

Code:
Sub ToggleCurrencySymbol()
  With Range("E:E, H:J, P:P")
    If .Cells(2).NumberFormat Like "*€*" Then
      .NumberFormat = Replace(.Cells(2).NumberFormat, "€", "£")
    Else
      .NumberFormat = Replace(.Cells(2).NumberFormat, "£", "€")
    End If
  End With
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top