Using Defined name and/or direct formula in VBA

[Schturman]

Hi.
I need some help with VBA code that should copy value from one cell to another.
I have Defined name named ExtractCurrency with formula:

=RIGHT(GET.CELL(53,ינואר!$I8),3)

It working perfectly with direct formula in the sheet:

=(I8*SWITCH(ExtractCurrency,"CHF",$M$5,"GBP",Live_Exchange_Rate!$H$5,"BGN",Live_Exchange_Rate!$A$16,"SEK",Live_Exchange_Rate!$H$7,"HUF",Live_Exchange_Rate!$C$10/100,"GEL",Live_Exchange_Rate!$A$13)/$M$3)*(1+$O$2)

First it checking which currency in use, then doing conversion to $
Because currency ration changing I trying to implement it to VBA code that just give me a result and will not change it when currency ration changing.. but without success...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range
  
'   See if any cells updated in column I
    Set rng = Intersect(Target, Range("I8:I55"))

    If rng Is Nothing Then Exit Sub

    Application.EnableEvents = False

    If Not rng Is Nothing Then
    '   Loop though all updated rows in column I
        For Each cell In rng
           If IsNumeric(cell.Value) And cell.Value > 0 Then
             If Range("ExtractCurrency").Value = "CHF"
                 cell.Offset(0, 1) = (cell * Range("$M$5") / Range("$M$3")) * (1 + Range("$O$2"))
                 Else
                   If Range("ExtractCurrency").Value = "GBP"
                     cell.Offset(0, 1) = (cell * Range("Live_Exchange_Rate!$H$5") / Range("$M$3")) * (1 + Range("$O$2"))
                      Else
                        If Range("ExtractCurrency").Value = "BGN"
                          cell.Offset(0, 1) = (cell * Range("Live_Exchange_Rate!$A$16") / Range("$M$3")) * (1 + Range("$O$2"))
                           Else
                            If Range("ExtractCurrency").Value = "SEK"
                              cell.Offset(0, 1) = (cell * Range("Live_Exchange_Rate!$H$7") / Range("$M$3")) * (1 + Range("$O$2"))
                            
                            End If
                        End If
                   End If
             End If
           Else
               Range(cell.Offset(0, 1), cell.Offset(0, -1)) = ""
           End If
        Next cell
    End If

    Application.EnableEvents = True 'reenable events
End Sub

Can someone help me to fix it or maybe suggest a better variant for code or maybe I can use my direct formula inside the code ?
Thanks

 

[Schturman]

Hi
Can you help me with something else please
I want to implement to this code also the automatic changing of currency format

For example if I change D2 from CHF to HUF and start to type number in A14 it will automatically will change format to "#,##0.00 [$HUF-hu-Hu]" (for now I do it manually when I need to change a format)
If I do it from Conditional formatting, it change all the Range (A2:A15), but I need only to the cell when I type the number in this range
I tried Implement this code, but it don't do nothing...

If Range("$D$2") <> "" Then
Select Case Range("$D$2")
  Case "GBP"
    cell.NumberFormat = "#,##0.00 [$GBP-en-GB]"
  Case "HUF"
    cell.NumberFormat = "#,##0.00 [$HUF-hu-Hu]"
End Select
End If

Any idea how to do this ?
Thanks

 

[Schturman]

Never mind, I fixed it

    If Range("$J$3") <> "" Then
        Select Case Range("$J$3")
        Case "CHF"
          cell.NumberFormat = "#,##0.00 [$CHF-fr-CH]"
        Case "GBP"
          cell.NumberFormat = "#,##0.00 [$GBP-en-GB]"
        Case "BGN"
          cell.NumberFormat = "#,##0.00 [$BGN-bg-BG]"
        Case "SEK"
          cell.NumberFormat = "#,##0.00 [$SEK-sv-SE]"
        Case "HUF"
          cell.NumberFormat = "#,##0.00 [$HUF-hu-Hu]"
        Case "GEL"
          cell.NumberFormat = "#,##0.00 [$GEL-ka-GE]"
        End Select
    End If