Currency Conversion VBA/Formula - Please help !!

Mike_VBA

Board Regular
Joined
Dec 27, 2008
Messages
70
Hi Guys,

Happy Belated Halloween!

I have a query.

I have two tables, One with Employee Information and expenses paid to them in different currencies and another table with the average currency rate for the month. How do I convert only the totals of each employee to reflect a certain currency in conversion (i.e., Totals shown in SGD,AUD or HKD).

39223534.png


CURRENCY

42520012.png


The need is to generate the subtotal/grand total based on a single currency (either SGD,AUD,HKD), by converting the multiple currencies in accordance to the date. Any ideas guys! Much appreciated.. Cheers to DonkeyOte and the other guys out here..
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
How about inserting a column next to each Year and then lopping through each row in the column and checking for value in Currency Column (AUD,HKD) and performing a lookup calculation? Then sum it at every last blank currency column and copy it to column on the left?

Although, that does not seem as a viable solution.
 
Upvote 0
I came across this on a vanity search...

I don't really understand the work flow you're after ? Are you looking to:

a) use Code to create sub totals & grand totals from scratch (ie insert rows)
b) use standard SubTotal functionality to cater for row insertions but use VBA to adjust/insert the formulae on those rows
c) something else entirely ?

I also don't really understand the relevance of Column C given you can seemingly have multiple values per row.
If that is the case then it would seem you do not have a 1:1 relationship between row & rate.
This is important point because if it is 1:1 then the subsequent conversion calculation is simplified somewhat.

Final points:
i) always better to post a display than can be copied to Excel (eg ExcelJeanie) rather than posting outright images - people don't generally have the time (and/or inclination) to recreate lengthy data sets
ii) always a good idea to outline expected results
 
Upvote 0
The workflow is

a) Get the data from Access. - Done
b) Format it as it looks. - Done using VBA
c) Provide an option for the user to change between currencies only at the subtotal/grandtotal levels. - Need some clarity.

I did get this done using Pivot VBA, but I am confusing on how to work on this.

I have attached the Excel and DB (1 mb) to these links:

Excel
Access DB

(Hope links are ok, Excel/Jeanie is not working well on my App for some reason, will post like that once I fix it)

Column C is totally irrelevant, something I bought from Access for no use.

Expected results in basically adding three controls ('Convert to AUD',HKD,SGD) and calculate only subtotals of each person by converting the currencies of one person into one currency (HKD,SGD,AUD) and totaling it at the subtotals.
 
Last edited:
Upvote 0
Hi, I don't have a great deal of time at the moment to work on this but formula wise:

Code:
E5:
=IF(E$1="","",SUMPRODUCT(E$2:E4*SUMIFS(INDEX(Sheet1!$C$2:$E$13,0,MATCH("*SGD",Sheet1!$C$1:$E$1,0)),Sheet1!$A$2:$A$13,E$1,Sheet1!$B$2:$B$13,$D$2:$D4))-SUMIF($A$1:$A4,"* Total",E$1:E4))

which can be applied to all Subtotal cells - above is for SGD conversion (denoted by *SGD)
[note I had to correct dates in row 1 - ie 2 Jan to 1 Feb]

Obviously you could apply this using VBA (modifying wildcard) but truth be told it's not efficient and I would say you would be best served using VBA Arrays to do this. I will try and post back later if not resolved in the meantime by others.
 
Upvote 0
I did try it, although yes the accuracy is questionable (Not hitting the right number). I will lookup on the VBA Arrays you spoke of.. Shall wait for a response..
 
Upvote 0
I did try it, although yes the accuracy is questionable (Not hitting the right number)

To be clear, the formula provided works it's simply that as an approach it's inefficient.
If you think otherwise then it would be worthy of further investigation.
 
Upvote 0
My bad, its perfect - Can I place this is R1C1 notation Loop? Loop through every subtotal row, then calculate range hit for formulae and apply? What do you think?
 
Upvote 0
Using the sample file... something like the below might work... you would pass the currency string to the routine eg Call AmendTotals("SGD")

Rich (BB code):
Sub AmendTotals(strCurr As String)
    Dim rngT As Range, rngArea As Range
    Set rngT = Intersect(ActiveSheet.UsedRange, Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow)
    For Each rngArea In rngT.Areas
        With rngArea.Offset(, 4).Resize(, rngArea.Columns.Count - 4)
            .FormulaR1C1 = "=IF(R1C=""""," & _
                                """""," & _
                                "SUMPRODUCT(R2C:R[-1]C*" & _
                                    "SUMIFS(" & _
                                        "INDEX(Sheet1!R2C3:R13C5,0,MATCH(""*" & strCurr & """,Sheet1!R1C3:R1C5,0))," & _
                                        "Sheet1!R2C1:R13C1,R1C," & _
                                        "Sheet1!R2C2:R13C2,R2C4:R[-1]C4" & _
                                    ")" & _
                                ")" & _
                                "-IF(RC1<>""Grand Total"",SUMIF(R1C1:R[-1]C1,""* Total"",R1C:R[-1]C))" & _
                            ")"
            .Value = .Value 'to override formulae with results (comment out to maintain formulae)
        End With
    Next rngArea
    Set rngT = Nothing
End Sub

the above is not meant as a complete solution (ie no handlers in place etc...)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top