Convert between two currencies by the click of a button in the same cells

Question

I want to create a function in my Excel workbook that converts all the cells in the three sheets I am currently using with a currency format from one currency (SLL) to another (DKK) by the click of a button (the same button converts in SLL or in DKK depending on which currency the values currently are in).

My code is:

Sub convertcurrency()

    Dim userrate1 As Long
    Dim userrate2 As Long

    For Each cell In ActiveWorkbook.Worksheets

        userrate1 = 625

        If Cells.NumberFormat = "DKK" & "$ #,##0.00" _
        Then cell.Value = "SLL" & userrate1 * cell.Value

        ElseIf Cells.NumberFormat = "SLL" & "$ #,##0.00" _
        Then cell.Value = "DKK" & (1 / userrate1) * cell.Value _

    End If

End Sub

But it's not working. The error is "Compile error. Else without if". But how can I use else without if, if I need to include the second restriction.

score 0 · Answer 1 · answered Jul 14 '15 at 10:09

0

Sub test()
    Dim userrate1 As Long
Dim userrate2 As Long

For Each cell In ActiveWorkbook.Worksheets

    userrate1 = 625

    If Cells.NumberFormat = "DKK" & "$ #,##0.00" _
    Then cell.Value = userrate1 * cell.Value

        If Cells.NumberFormat = "SLL" & "$ #,##0.00" _
        Then cell.Value = (1 / userrate1) * cell.Value _



End Sub

try this

answered Jul 14 '15 at 10:09

Sandesh Jadhav M

65
10

If then statement used here is a single standalone statement. Anything written after then the scope of the if statement gets limited to that line only. – Sandesh Jadhav M Jul 14 '15 at 10:10
Hi Sandesh. I've modified the code to: Sub convertcurrency() Dim userrate1 As Long Dim userrate2 As Long For Each cell In ActiveWorkbook.Worksheets userrate1 = 625 If Cells.NumberFormat = "DKK" & "$ #,##0.00" _ Then cell.Value = "SLL" & userrate1 * cell.Value If Cells.NumberFormat = "SLL" & "$ #,##0.00" _ Then cell.Value = "DKK" & (1 / userrate1) * cell.Value Next End Sub. No error codes, but it is not working. – Saud Jul 14 '15 at 10:11
Is the currecny SLL or DKK part of the cell value? – Sandesh Jadhav M Jul 14 '15 at 10:15
What is the default. The number format shared by you does not exit in Excel. – Sandesh Jadhav M Jul 14 '15 at 10:17
The default is DKK, but does it matter? Currently all the "currency-formated" cells are set to SLL. Just recorded a macro to see what happens when convert from SLL to DKK and the output is: Range("G15").Select Selection.NumberFormat = "[$DKK] #,##0". I've tried to implement this in my code, but it isn't working. – Saud Jul 14 '15 at 10:20
Sub convertcurrency() Dim userrate1 As Long Dim userrate2 As Long For Each cell In ActiveWorkbook.Worksheets userrate1 = 625 If Cells.NumberFormat = "[$DKK] #,##0.00" _ Then cell.Value = "SLL " & userrate1 * cell.Value If Cells.NumberFormat = "[$SLL] #,##0.00" _ Then cell.Value = "DKK " & (1 / userrate1) * cell.Value Next End Sub – Saud Jul 14 '15 at 10:20
Try these number formats... [$DKK] #,##0.00 and [$SLL] #,##0.00 – Sandesh Jadhav M Jul 14 '15 at 10:20
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/83187/discussion-between-sandesh-jadhav-m-and-saud). – Sandesh Jadhav M Jul 14 '15 at 10:24

score 0 · Answer 2 · 2015-07-14T11:10:42.023

I'm not sure about the functionality but this corrects the syntax.

Sub convertCurrency()

    Dim userrate1 As Double, userrate2 As Double
    Dim cr As Range, currRng As Range, ws As Worksheet

    userrate1 = 625
    userrate2 = 1 / userrate1

    For Each ws In ActiveWorkbook.Worksheets
        With ws
            On Error Resume Next
            Set currRng = .Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
            On Error GoTo 0
            If Not currRng Is Nothing Then
                For Each cr In currRng
                    If cr.NumberFormat = "DKK" & "$ #,##0.00" Then
                        cr.Value = "SLL" & userrate1 * cr.Value           '<-one of these should probably be userrate2
                    ElseIf cr.NumberFormat = "SLL" & "$ #,##0.00" Then
                        cr.Value = "DKK" & (1 / userrate1) * cr.Value     '<-one of these should probably be userrate2
                    Else
                        'send non-matching number format to the VBE's Immediate window (Ctrl+G) to see what was missed
                        debug.print cr.numberformat
                    End If
                Next cr
            End If

            On Error Resume Next
            Set currRng = .Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)
            On Error GoTo 0
            If Not currRng Is Nothing Then
                For Each cr In currRng
                    If cr.NumberFormat = "DKK" & "$ #,##0.00" Then
                        cr.Value = "SLL" & userrate1 * cr.Value           '<-one of these should probably be userrate2
                    ElseIf cr.NumberFormat = "SLL" & "$ #,##0.00" Then
                        cr.Value = "DKK" & (1 / userrate1) * cr.Value     '<-one of these should probably be userrate2
                    Else
                        'send non-matching number format to the VBE's Immediate window (Ctrl+G) to see what was missed
                        debug.print cr.numberformat
                    End If
                Next cr
            End If
        End With
    Next ws

End Sub

I've attempted to correct your logic the best that I could from your description in the original question and the comments. The Range.SpecialCells method will only look at numbers that are not produces by formulas which is what your original code indicated.

Hi Jeeped. The button inside every sheet (which is the same across all the sheets) should convert all the "currency-formated" values in each cell from SLL to DKK (if the current values are in SLL) and from DKK to SLL (if the current values are in DKK). Why doens't "For each cell (...)" make sense? Cells are in different formats, not only in currency format. — Saud, Jul 14 '15 at 11:03
a) you never declared `cell` so it was an object b) for each Cell in [Worksheets](https://msdn.microsoft.com/en-us/library/office/aa224508(v=office.11).aspx) means that Cell was a worksheet, not a cell or [Range object](https://msdn.microsoft.com/en-us/library/office/ff838238.aspx) no matter what you named it d) you never closed the [For Each...Next Statement](https://msdn.microsoft.com/en-us/library/5ebk1751.aspx) — , Jul 14 '15 at 11:07
Sry, I forgot to mention that some of the "currency formated" values do have contain formulas, while others do not. Regarding "userrate2", can't I just use the reciprocal value of the first one, if I am only converting between to currencies or do I explicitly have to define them? — Saud, Jul 14 '15 at 11:07
OK, I added a second iteration for numbers produced by formulas — , Jul 14 '15 at 11:11
I've tried the second iteration. It came up with an error. Then I made these changes: "DKK" & "$ #,##0.00" => "[$DKK] #,##0" "SLL" & "$ #,##0.00" = "[$SLL] #,##0" and 4 values were succesfully converted, but the remaining 200-300 didn't convert. And when I clicked on the button again it didn't change back to the original currency. — Saud, Jul 15 '15 at 10:18
It would appear that the number formats are not what you believe them to be. Take a look at the VBE's Immediate window (Ctrl+G) for the non-matching output that my routine sent there. Edit your original question to include samples of the number format strings. — , Jul 15 '15 at 10:31
Hi Jeeped. I'm not entirely sure I understand what you mean by samples of the number format strings. Is it possible to somehow upload the excel file? The values seem to be in the right format. — Saud, Jul 31 '15 at 08:35

Convert between two currencies by the click of a button in the same cells

2 Answers2