• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Converting currency formats

Gandalf

Gandalf

Member
Dear All, it has been quite some time since I last posted here so it is good to be back.

I have been preparing a workbook for my wife which looks at all staff and department costs and incomes to see which are profitable and which are not. As we are currently based in Mauritius for a UK based university all the currencies are in GBP but she would like to be able to look at the figures in both GBP and Mauritian Rupees.

What I have tried to do is create some VBA which will select all currency formatted cells and convert them to Rupees if they are in GBP or vice-versa.

The code I have is below

Code: 
Private Sub Currency_Click()
'Multiply by the exchange rate
getrate = InputBox("Enter the current rate")
If getrate = "" Then Exit Sub
userate = CDec(Format(getrate, "0.0000"))
For Each Cell In Range("Currency")
If Not Cell = "£" Then Cell.Value = Format(Cell * 50, "Rs" + "0.00")
If Cell = "Rs" Then Cell.Value = Format(Cell / 50, "£" + "0.00")
Next cell

End Sub

"Currency" is a named range from multiple worksheets.

Why do I always get this error message?upload_2016-8-4_11-34-45.png

Thank you for any help.
 
I've tried this, but it is obviously wrong. anyone any ideas why.

Code: 
Private Sub CommandButton1_Click()

Dim CurrRng As Range

Set CurrRng = Cells.NumberFormat("$#,#00.00")

For Each cell In CurrRng
   
    If cell.Format([£], "#,#00.00") Then
   
    cell.Format ([MUR]) And cell.Value * 50
   
    ElseIf cell.Format([MUR], "#.#00.00") Then
   
    cell.Format ([£]) And cell.Value / 50
   
    End If
   
    Next
   

End Sub

Thank you
 
Moving on but not very far :confused:.
I tried writing the code below (actually the first part which works fine was written by someone else, the second bit was written by me and doesn't work at all)
Code: 
Private Sub CommandButton1_Click()

Dim sh As Worksheet, cel As Range, rng As Range

For Each sh In Worksheets
    Set rng = Nothing
    For Each cel In sh.UsedRange.Cells
        If Left(cel.NumberFormat, 1) = "$" Then
            If rng Is Nothing Then
                Set rng = cel
            Else
                Set rng = Union(rng, cel)
            End If
        End If
    Next
    If Not rng Is Nothing Then _
    ActiveWorkbook.Names.Add Name:=sh.Name & "Curr", _
        RefersTo:=sh.Name & "!" & rng.Address

Next

    Dim allRngs As Range
    Set allRngs = Union(CampusCurr, BusCurr, EduCurr)
        For Each cell In Range("allRngs")
            If Left(cell, 1) = "£" Then
            Left(cell, 1) = "MUR" And cell.Value * 50
            Else: Left(cell, 1) = "£" And cell.Value / 50
            End If
        Next
End Sub

What it is meant to do, my bit, is join all the Named Ranges in the first part into a range called "allRngs" then go through each cell in the allRngs and either change a £ sign to MUR and multiply the cell value by 50 or vice-versa.
But... it doesn't. I get run time error 424 object required at the underlined italic Has anyone any ideas. Thank you.
 

Hi !

This error means a bad code syntax or a typo in the object name !
So it depends on the line where error occurs …
​
 
Hi Marc,
The error occurs on the line "Set allRngs = Union(CampusCurr, BusCurr, EduCurr)". As a real novice I don't think it is a typo so probably syntax. I've tried putting the range names in quotes and a lot of other things with no luck.

Thanks for the help
 
When errors occurs, check in Locals window if the xxxCurr variables
are initialized as Range and not equal to Nothing …
As a name range must be called in VBA Range("namerange") …

And on what I saw in codelines above this one,
I think - maybe I'm wrong - it's from a bad logic,
all code is to be refreshed from new …
​
 
Hi ,

I do not have any idea about the MUR part of it , but as far as the £ currency symbol is concerned , your file has the following format for cells containing this currency symbol :

"£"#,##0.00;[Red]"£"#,##0.00

The £ currency symbol is therefore not the first character in the format string.

Secondly , several blank cells can also have this currency format ; rather than include those cells also in the format change , it is probably better to include a check for whether the cells have a non-zero value ; you can also change this check to see whether the cells are empty or you can dispense with this check altogether. In any case , changing the following line of code :

If Left(cel.NumberFormat, 1) = "$" Then

to

If ((Left(cel.NumberFormat, 3) = """£""") And (cel.Value <> 0)) Then

should help.

Narayan
 
I'm really sorry about this. I have tried to simplify things and rather than have vba do things in 1 sub I have split it into 2 with 2 command buttons. The first part seems to work fine and produces a range of the right cells. The scond part doesn't work, I get a "next without for error". Any ideas why?

Code: 
Private Sub CommandButton2_Click()

  ' my stuff to change individual cells in range
  ' loop through all currency formatted cells (range = "Curr")
  ' if first character is £, convert to  Mauritian Rupees (R) and multiply by exchange rate (roughly 50 at present _
  ' but when it works it hopefully will use input box for exact rate)
  ' if first character is R change to £ and divide by 50 (or input box)
  Dim rng As Range
  Dim cell As Range
  Set rng = Range("Curr")
  For Each cell In rng
  If Left(cell.NumberFormat, 3) = """£""" And Not IsEmpty(cell.Value) Then
  Left(cell.NumberFormat, 3) = """R""" And cell.Value * 50
  Else
  If Left(cell.NumberFormat, 3) = """R""" And Not IsEmpty(cell.Value) Then
  Left(cell.NumberFormat, 3) = """£""" And cell.Value / 50
  End If
  Next
 
 
 
  'This bit works - not written by me - returns the macro to the active cell
  Dim s As Range
  Set s = Selection
  s.Parent.Activate
  s.Select 'NOT Activate - possibly more than one cell!
End Sub
 
Hi ,

I do not know whether this revised code will work , but the code you posted had an ENDIF missing.

Try this :
Code: 
Private Sub CommandButton2_Click()
'      my stuff to change individual cells in range
'      loop through all currency formatted cells (range = "Curr")
'      if first character is £, convert to  Mauritian Rupees (R) and multiply by exchange rate (roughly 50 at present _
'      but when it works it hopefully will use input box for exact rate)
'      if first character is R change to £ and divide by 50 (or input box)
    Dim rng As Range
    Dim cell As Range
   
    Set rng = Range("Curr")
    For Each cell In rng
        If Left(cell.NumberFormat, 3) = """£""" And Not IsEmpty(cell.Value) Then
          Left(cell.NumberFormat, 3) = """R""" And cell.Value * 50
        Else
            If Left(cel.NumberFormat, 3) = """R""" And Not IsEmpty(cell.Value) Then
                Left(cell.NumberFormat, 3) = """£""" And cell.Value / 50
            End If
        End If
    Next
 
'      This bit works - not written by me - returns the macro to the active cell
    Dim s As Range
    Set s = Selection
    s.Parent.Activate
    s.Select 'NOT Activate - possibly more than one cell!
End Sub
Narayan
 
A quick thank you for all suggestions and apologies for not reposting sooner (Mauritius seems to have intermittent connection problems). I finally worked out with your help that using ranges across multiple worksheets, or trying to use a range across multiple worksheets doesn't seem to work so I had to use multiple bits of code addressing each range separately and specifically. At least I have learned something. Thanks again.
 
You must log in or register to reply here.
Back
Top