Select Case - Named Ranges

trux101

New Member
Joined
Feb 10, 2016
Messages
19
I have a spreadsheet that contains currency name in col A e.g. USD, AUD etc. Col B contains the amount that needs to be converted into GBP. The calculations will happen in the VBA code below and results stored in col C, D and E</SPAN>


Sub SoSo()</SPAN></SPAN>
Dim Rng As Range</SPAN></SPAN>
Dim rNum As Long</SPAN></SPAN>
Dim LRow As Long</SPAN></SPAN>
LRow = Range("Ccy").Rows.Count</SPAN></SPAN>

For rNum = 1 To LRow + 1</SPAN></SPAN>

Select Case Range("A" & rNum).Value</SPAN></SPAN>
Case "USD"</SPAN></SPAN>
Range("C" & rNum).Value = Range("B" & rNum) / 1.555</SPAN></SPAN>
Range("D" & rNum).Value = Range("B" & rNum) * 1.055</SPAN></SPAN>
Range("E" & rNum).Value = Range("B" & rNum) * 1.015</SPAN></SPAN>

Case "AUD"</SPAN></SPAN>
Range("C" & rNum).Value = Range("B" & rNum) / 2.015</SPAN></SPAN>
Range("D" & rNum).Value = Range("B" & rNum) * 1.055</SPAN></SPAN>
Range("E" & rNum).Value = Range("B" & rNum) * 1.015</SPAN></SPAN>

End Select</SPAN></SPAN>

Next rNum</SPAN></SPAN>

MsgBox "Completed"</SPAN></SPAN>

End Sub</SPAN></SPAN>

The overall spreadsheet will contain about 90,000 rows of data that will need the same procedure to take place. It’s preferable to use named ranges as below instead of col A, B etc.</SPAN>

  • Col A = Ccy</SPAN>
  • Col B = Cost</SPAN></SPAN>
  • Col C = Cost_In_GBP</SPAN></SPAN>
  • Col D = Commision_1</SPAN></SPAN>
  • Col E = Commision_2</SPAN></SPAN>

Questions</SPAN>
Is there a method of using the named ranges for this procedure, currently using the above named ranges is breaking the code?</SPAN>
Is there a method of increasing its efficiency and speed of execution, currently it’s taking several minutes?</SPAN>
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
Hi and welcome to the MrExcel Message Board.

You could use named ranges but if you want to improve execution speed then there is another way.

VBA is slow if it has to keep reading and writing to the worksheet. The way to speed it up is to read everything in one go then write it all back in one go. Basically, you just transfer the column data into a VBA array.

However I have a question. The commissions seem to be calculated as a percentage of the original currency and not as a percentage of the GBP value. Is that intentional?
 

trux101

New Member
Joined
Feb 10, 2016
Messages
19
Thank you for the reply RickXL

I'm very new to VBA not sure how that code can be altered to make it work with named ranges. The commission has other calculations to it and you're correct afterwards will also need to be converted to GBP - but I removed those extra elements to make it clearer on the issue I have with the code. Look forward to your feedback!
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
Hi,

I am more of a VBA person than a worksheet one so I have to say that Named Ranges are not something I would choose to use. You need to make them dynamic before they are very useful. I would probably use a Table instead.

However, if you want to use a named Range then I think this works:
Code:
Sub SoSoRange()
    Dim Rng As Range
    Dim rNum As Long
    Dim LRow As Long
    LRow = Range("Ccy").Rows.Count
    
    For rNum = 1 To LRow + 1
    
        Select Case Range("Ccy")(rNum, 1).Value
            Case "USD"
                Range("Cost_In_GBP")(rNum, 1).Value = Range("Cost")(rNum, 1) / 1.555
                Range("Commision_1")(rNum, 1).Value = Range("Cost")(rNum, 1) * 1.055
                Range("Commision_2")(rNum, 1).Value = Range("Cost")(rNum, 1) * 1.015
            
            Case "AUD"
                Range("Cost_In_GBP")(rNum, 1).Value = Range("Cost")(rNum, 1) / 2.015
                Range("Commision_1")(rNum, 1).Value = Range("Cost")(rNum, 1) * 1.055
                Range("Commision_2")(rNum, 1).Value = Range("Cost")(rNum, 1) * 1.015
        
        End Select
    
    Next rNum
    
    MsgBox "Completed"

End Sub
If you want it to run quicker then you could use this which still relied on named Ranges:
Code:
Sub SoSoArray()
    Dim Rng As Range
    Dim rNum As Long
    Dim LRow As Long
    Dim Ccy As Variant
    Dim Cost As Variant
    Dim Cost_In_GBP As Variant
    Dim Commision_1 As Variant
    Dim Commision_2 As Variant
    
    LRow = Range("Ccy").Rows.Count
    Ccy = Range("Ccy")
    Cost = Range("Cost")
    ReDim Cost_In_GBP(1 To LRow, 1 To 1)
    ReDim Commision_1(1 To LRow, 1 To 1)
    ReDim Commision_2(1 To LRow, 1 To 1)
    
    For rNum = 1 To LRow
    
        Select Case Ccy(rNum, 1)
            Case "USD"
                Cost_In_GBP(rNum, 1) = Cost(rNum, 1) / 1.555
                Commision_1(rNum, 1) = Cost(rNum, 1) * 1.055
                Commision_2(rNum, 1) = Cost(rNum, 1) * 1.015
            
            Case "AUD"
                Cost_In_GBP(rNum, 1) = Cost(rNum, 1) / 2.015
                Commision_1(rNum, 1) = Cost(rNum, 1) * 1.055
                Commision_2(rNum, 1) = Cost(rNum, 1) * 1.015
        
        End Select
    
    Next rNum
    
    Range("Cost_In_GBP") = Cost_In_GBP
    Range("Commision_1") = Commision_1
    Range("Commision_2") = Commision_2
    
    MsgBox "Completed"

End Sub
My personal choice would be to not use Named Ranges so I would probably use something like this:
Code:
Sub SoSoArray2()

    Dim rNum            As Long
    Dim LRow            As Long
    Dim Ccy             As Variant
    Dim Cost            As Variant
    Dim Cost_In_GBP     As Variant
    Dim Commision_1     As Variant
    Dim Commision_2     As Variant
    
    LRow = Cells(Rows.Count, "A").End(xlUp).Row
    Ccy = Range("A2:A" & LRow)
    Cost = Range("B2:B" & LRow)
    ReDim Cost_In_GBP(1 To UBound(Ccy), 1 To 1)
    ReDim Commision_1(1 To UBound(Ccy), 1 To 1)
    ReDim Commision_2(1 To UBound(Ccy), 1 To 1)
    
    For rNum = 1 To UBound(Ccy)
    
        Select Case Ccy(rNum, 1)
            Case "USD"
                Cost_In_GBP(rNum, 1) = Cost(rNum, 1) / 1.555
                Commision_1(rNum, 1) = Cost(rNum, 1) * 1.055
                Commision_2(rNum, 1) = Cost(rNum, 1) * 1.015
            
            Case "AUD"
                Cost_In_GBP(rNum, 1) = Cost(rNum, 1) / 2.015
                Commision_1(rNum, 1) = Cost(rNum, 1) * 1.055
                Commision_2(rNum, 1) = Cost(rNum, 1) * 1.015
        
        End Select
    
    Next rNum
    
    Range("C2").Resize(UBound(Cost_In_GBP), 1) = Cost_In_GBP
    Range("D2").Resize(UBound(Cost_In_GBP), 1) = Commision_1
    Range("E2").Resize(UBound(Cost_In_GBP), 1) = Commision_2
    
    MsgBox "Completed"

End Sub
 
Last edited:

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
If you choose to use a Table then the following code will work for the current worksheet.

To change your data to a table just select a cell in the data and hit Ctrl + T then confirm the range.

I renamed my table to CcyTable - it defaulted to Table1.

Code:
Sub SoSoTable2()
    Dim rNum            As Long
    Dim Ccy             As Variant
    Dim Cost            As Variant
    Dim Cost_In_GBP     As Variant
    Dim Commision_1     As Variant
    Dim Commision_2     As Variant
    
    Ccy = Range("CcyTable[Ccy]")
    Cost = Range("CcyTable[Cost]")
    ReDim Cost_In_GBP(1 To UBound(Ccy), 1 To 1)
    ReDim Commision_1(1 To UBound(Ccy), 1 To 1)
    ReDim Commision_2(1 To UBound(Ccy), 1 To 1)
    
    For rNum = 1 To UBound(Ccy)
        Select Case Ccy(rNum, 1)
            Case "USD"
                Cost_In_GBP(rNum, 1) = Cost(rNum, 1) / 1.555
                Commision_1(rNum, 1) = Cost(rNum, 1) * 1.055
                Commision_2(rNum, 1) = Cost(rNum, 1) * 1.015
            Case "AUD"
                Cost_In_GBP(rNum, 1) = Cost(rNum, 1) / 2.015
                Commision_1(rNum, 1) = Cost(rNum, 1) * 1.055
                Commision_2(rNum, 1) = Cost(rNum, 1) * 1.015
        End Select
    Next rNum
    
    Range("CcyTable[Cost_In_GBP]") = Cost_In_GBP
    Range("CcyTable[Commision_1]") = Commision_1
    Range("CcyTable[Commision_2]") = Commision_2
    
    MsgBox "Completed"

End Sub
Using Tables has some advantages:
1. You can insert extra lines in front or on top of the table and the code does not need changing.
2. Any formulas in the table will automatically be copied down when entered.
3. You can add a totals row without requiring a change in the code.
4. You can use table name and column headings everywhere in the code.
5. No Named Ranges required.
6. You can add rows and the table will automatically expand. No code changes necessary.
7. I think it improves the readability of the code.
8. The Variant Arrays can be directly assigned to the column without having to count rows and subtract start rows etc.
 

trux101

New Member
Joined
Feb 10, 2016
Messages
19
Rick this is amazing and it works perfectly! I've tested the top 3 solutions and below are the seconds each takes to complete. </SPAN>

50,000 Rows</SPAN>
Solution 1 - 61.2 sec </SPAN></SPAN>
Solution 2 - 0.16 sec</SPAN></SPAN>
Solution 3 – 0.16 sec</SPAN></SPAN>

Now suppose I wanted to have the exchange rates in the excel sheet range rather than hardcoded as it is currently, what adjustment would I need to solution 2 (the one using named ranges) to make it work? By having them hardcoded as I do means each time the FX rates change I have to go into my code to ammend.</SPAN></SPAN>

Thanks</SPAN></SPAN>
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
Where would you like to put your list of exchange rates?

On the same worksheet or on a different one?

Also, which coding solution do you want to go with?

I just added a Table with exchange rates to the worksheet with the data and it worked out quite easily. I called the new Table "Rates":
Code:
Sub SoSoTable3()
    Dim rNum            As Long
    Dim Ccy             As Variant
    Dim Cost            As Variant
    Dim Cost_In_GBP     As Variant
    Dim Commision_1     As Variant
    Dim Commision_2     As Variant
    Dim Dic             As Object
    Dim c               As Range
    
    Set Dic = CreateObject("Scripting.Dictionary")
    For Each c In Range("Rates[Ccy]")
        Dic(c.Value) = c.Offset(0, 1).Value
    Next
    
    Ccy = Range("CcyTable[Ccy]")
    Cost = Range("CcyTable[Cost]")
    ReDim Cost_In_GBP(1 To UBound(Ccy), 1 To 1)
    ReDim Commision_1(1 To UBound(Ccy), 1 To 1)
    ReDim Commision_2(1 To UBound(Ccy), 1 To 1)
    
    For rNum = 1 To UBound(Ccy)
        Cost_In_GBP(rNum, 1) = Cost(rNum, 1) * Dic(Ccy(rNum, 1))
        Commision_1(rNum, 1) = Cost(rNum, 1) * 1.055
        Commision_2(rNum, 1) = Cost(rNum, 1) * 1.015
    Next rNum
    
    Range("CcyTable[Cost_In_GBP]") = Cost_In_GBP
    Range("CcyTable[Commision_1]") = Commision_1
    Range("CcyTable[Commision_2]") = Commision_2
    
    MsgBox "Completed"

End Sub
<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FFFFFF;background-color: #5B9BD5;;">Ccy</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FFFFFF;background-color: #5B9BD5;;">Cost</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FFFFFF;background-color: #5B9BD5;;">Cost_In_GBP</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FFFFFF;background-color: #5B9BD5;;">Commision_1</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FFFFFF;background-color: #5B9BD5;;">Commision_2</td><td style="text-align: right;;"></td><td style=";">Ccy</td><td style=";">Rate</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">USD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">81</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">56.144988</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">85.455</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">82.215</td><td style="text-align: right;;"></td><td style=";">USD</td><td style="text-align: right;;">0.693148</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">USD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">20</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">13.86296</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">21.1</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">20.3</td><td style="text-align: right;;"></td><td style=";">GBP</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">USD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">89</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">61.690172</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">93.895</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">90.335</td><td style="text-align: right;;"></td><td style=";">AUD</td><td style="text-align: right;;">0.4911</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">USD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">60</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">41.58888</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">63.3</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">60.9</td><td style="text-align: right;;"></td><td style=";">EUR</td><td style="text-align: right;;">0.784759</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">USD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">81</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">56.144988</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">85.455</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">82.215</td><td style="text-align: right;;"></td><td style=";">CAD</td><td style="text-align: right;;">0.495372</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">USD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">24</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">16.635552</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">25.32</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">24.36</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">USD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">34</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">23.567032</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">35.87</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">34.51</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">USD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">99</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">68.621652</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">104.445</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">100.485</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">xxx</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">76</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">80.18</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">77.14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">USD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">51</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">35.350548</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">53.805</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">51.765</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">USD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">64</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">44.361472</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">67.52</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">64.96</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">AUD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">98</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">48.1278</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">103.39</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">99.47</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">AUD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">47</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">23.0817</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">49.585</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">47.705</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">AUD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">15</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">7.3665</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">15.825</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">15.225</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">AUD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">74</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">36.3414</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">78.07</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">75.11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">AUD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">0.9822</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">2.11</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">2.03</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">AUD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">78</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">38.3058</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">82.29</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">79.17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">AUD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">52</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">25.5372</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">54.86</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">52.78</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">AUD</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">56</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">27.5016</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">59.08</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">56.84</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="border-top: 1px solid black;background-color: #DDEBF7;;">USD</td><td style="text-align: right;border-top: 1px solid black;background-color: #DDEBF7;;">100</td><td style="text-align: right;border-top: 1px solid black;background-color: #DDEBF7;;">69.3148</td><td style="text-align: right;border-top: 1px solid black;background-color: #DDEBF7;;">105.5</td><td style="text-align: right;border-top: 1px solid black;background-color: #DDEBF7;;">101.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="background-color: #DDEBF7;;">Total</td><td style="text-align: right;background-color: #DDEBF7;;">1201</td><td style="text-align: right;background-color: #DDEBF7;;">694.527244</td><td style="text-align: right;background-color: #DDEBF7;;">1267.055</td><td style="text-align: right;background-color: #DDEBF7;;">1219.015</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Convert</p><br /><br />
 

trux101

New Member
Joined
Feb 10, 2016
Messages
19
  1. Using solution 2 – initially with the code below I was getting a runtime error 11 – divide by zero and so I deleted the zeros but in reality the zeros will be a regular occurrence so I need to figure some sort of error handler that replaces zero or blank Ccy with GBP and zero or blank Cost with 1. </SPAN></SPAN></SPAN>
  2. After manually removing the zero values I now have a runtime error 6</SPAN></SPAN></SPAN>


I’ve spent the last 2 hours trying to work this out with no luck! Where am I going wrong with this?</SPAN></SPAN>


Sub SoSoArray2 ()</SPAN></SPAN>
Dim Rng As Range</SPAN></SPAN>
Dim rNum As Long</SPAN></SPAN>
Dim LRow As Long</SPAN></SPAN>
Dim Ccy As Variant</SPAN></SPAN>
Dim Cost As Variant</SPAN></SPAN>
Dim Cost_In_GBP As Variant</SPAN></SPAN>
Dim Commision_1 As Variant</SPAN></SPAN>
Dim Commision_2 As Variant</SPAN></SPAN>
Dim Dic As Object</SPAN></SPAN>
Dim c As Range</SPAN></SPAN>

Set Dic = CreateObject("Scripting.Dictionary")</SPAN></SPAN>
For Each c In Range("FX_Tbl")</SPAN></SPAN>
Dic(c.Value) = c.Offset(0, 1).Value</SPAN></SPAN>
Next</SPAN></SPAN>

LRow = Range("Ccy").Rows.Count</SPAN></SPAN>
Ccy = Range("Ccy")</SPAN></SPAN>
Cost = Range("Cost")</SPAN></SPAN>

ReDim Cost_In_GBP(1 To LRow, 1 To 1)</SPAN></SPAN>
ReDim Commision_1(1 To LRow, 1 To 1)</SPAN></SPAN>
ReDim Commision_2(1 To LRow, 1 To 1)</SPAN></SPAN>

For rNum = 1 To LRow</SPAN></SPAN>
Cost_In_GBP(rNum, 1) = Cost(rNum, 1) / Dic(Ccy(rNum, 1))</SPAN></SPAN>
Commision_1(rNum, 1) = Cost(rNum, 1) * 1.055</SPAN></SPAN>
Commision_2(rNum, 1) = Cost(rNum, 1) * 1.015</SPAN></SPAN>
Next rNum</SPAN></SPAN>

Range("Cost_In_GBP") = Cost_In_GBP</SPAN></SPAN>
Range("Commision_1") = Commision_1</SPAN></SPAN>
Range("Commision_2") = Commision_2</SPAN></SPAN>

End Sub</SPAN></SPAN>
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
When I run your code it ran until it found my "xxx" currency then returned a Divide By Zero error.

Your previous code had a multiplication sign there so there was no problem. Could you use the reciprocal of the exchange rate as before?

Alternatively:
Code:
Sub SoSoArray2()
    Dim Rng             As Range
    Dim rNum            As Long
    Dim LRow            As Long
    Dim Ccy             As Variant
    Dim Cost            As Variant
    Dim Cost_In_GBP     As Variant
    Dim Commision_1     As Variant
    Dim Commision_2     As Variant
    Dim Dic             As Object
    Dim c               As Range
    
    Set Dic = CreateObject("Scripting.Dictionary")
    For Each c In Range("FX_Tbl")
        Dic(c.Value) = c.Offset(0, 1).Value
    Next
    
    LRow = Range("Ccy").Rows.Count
    Ccy = Range("Ccy")
    Cost = Range("Cost")
    
    ReDim Cost_In_GBP(1 To LRow, 1 To 1)
    ReDim Commision_1(1 To LRow, 1 To 1)
    ReDim Commision_2(1 To LRow, 1 To 1)
    
    For rNum = 1 To LRow
        If Dic.Exists(Ccy(rNum, 1)) Then
            If Dic(Ccy(rNum, 1)) <> 0 Then
                Cost_In_GBP(rNum, 1) = Cost(rNum, 1) / Dic(Ccy(rNum, 1))
                Commision_1(rNum, 1) = Cost(rNum, 1) * 1.055
                Commision_2(rNum, 1) = Cost(rNum, 1) * 1.015
            End If
        End If
    Next rNum
    
    Range("Cost_In_GBP") = Cost_In_GBP
    Range("Commision_1") = Commision_1
    Range("Commision_2") = Commision_2

End Sub
 

trux101

New Member
Joined
Feb 10, 2016
Messages
19
Rick I really appreciate this! One final thing - the program runs perfectly if the FX_Tbl is on the same sheet. What tweak is needed to get it to run on whatever worksheet the FX_Tbl is stored on?
 

Forum statistics

Threads
1,082,334
Messages
5,364,677
Members
400,810
Latest member
elbashka

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top