Convert EUR to GBP VBA Script

[mattbarb]

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

 

[Peter_SSs]

Do you have an exchange rate stored somewhere? Where?

"Change all the values". Where are "all the values"?

 

[mattbarb]

Hello, I literally just need to change the symbol and not the values. Sorry wasn't clear.

 

[Peter_SSs]

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.

 

[mattbarb]

Hi, Sorry values are across multiple columns of the sheet. Cell does not contain the currency value.

 

[mohadin]

Hi
Try something like this

 Selection.NumberFormat = "[$€-83C]#,##0.00"

OR

 Selection.NumberFormat = "[$EUR]#,##0.00"

 

[Peter_SSs]

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.

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