Can anybody help me?

[Sunshine]

I've been given the following question; Create the VBA procedure convertCurrency that converts one currency to another. It must work by converting an amount of currency in column A to an amount of currency in the column of the selected cell.
Example:
If 500 INR is to be converted to EUR the cell to be selected is D12:
11739

Can somebody give me a VBA code that works for this?
In the same Excel file i have a sheet with the currency rates....

[westconn1]

your rate table appears to be too incomplete to achieve a result for sample question
also i can not determine which cell in column A contains the amout, but i would assume it would be like

msgbox cellwithamount * activecell

[Sunshine]

I get the question as that I am supposed to write a macro that it works so that when a put in a value in for example field D12, I put in the value in INR and should get the macro to multiply this number with the exchange rates I have in another sheet and give me the number in EUR. The exchange rate table looks like this:
11740
The macro should when i select a cell, and the eg. press a button bring up an input box to take the amount to be converted.The currency to convert from is the currency in column A and the currency to convert to is the currency in the selected column.

[mikerickson]

You could use this UDF, with a formula like =ConvertCurrency(250, "EUR", "RUB")

Function ConvertCurrency(FromAmount As Double, FromCurrency As String, ToCurrency As String, Optional ConversionChart As Range) As Variant
Dim fromDKR As Double, toDKR As Double
Dim amountDKR As Double
If ConversionChart Is Nothing Then Set ConversionChart = Sheet1.Range("A5:I13")

With ConversionChart
On Error Resume Next
fromDKR = ConversionChart.Cells(2, Application.Match(FromCurrency, .Cells(1, 1).EntireRow, 0)).Value
toDKR = ConversionChart.Cells(2, Application.Match(ToCurrency, .Cells(1, 1).EntireRow, 0)).Value
On Error GoTo 0
End With
If fromDKR = 0 Or toDKR = 0 Then
ConvertCurrency = CVErr(xlErrValue)
Else
amountDKR = FromAmount / 100 * fromDKR
ConvertCurrency = 100 / toDKR * amountDKR
End If
End Function

[Sunshine]

Thanks a lot, but I don't get it to work to save it as an macro?... And I would really like to create a button that detects the location of the selected cell and then when I press it an inputbox occurs where I can can type how much I want to convert. Is this possible? :)

[Paul_Hossler]

Thanks a lot, but I don't get it to work to save it as an macro?... And I would really like to create a button that detects the location of the selected cell and then when I press it an inputbox occurs where I can can type how much I want to convert. Is this possible? :)

Very possible, but you need the enter/select the FromCurrency and the ToCurrency, as well as a number amount

How do you plan/want to do that? Or do you just want to do all of the currencies?

[Sunshine]

I want the code to understand that when i choose for example cell B7 the amount I enter in the input box should be converted from USD to DKR. And it should be possible to choose any of the cells and the code should get what I want to convert.

[westconn1]

you could use the selection change event, check the range of the selection is within the currency table, get the offsets of the target to find the to and from currencies, then do the lookups and conversion

try like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("b6:i13")) Is Nothing Then
amnt = InputBox("enter ammount to convert")
cfrom = WorksheetFunction.VLookup(Target.Offset(-Target.Row + 5), Sheets("lookuptable").Range("a4:b10"), 2, False) ' change sheet name to suit
cto = WorksheetFunction.VLookup(Target.Offset(, -Target.Column + 1), Sheets("lookuptable").Range("a4:b10"), 2, False)
Target.Value = amnt / cto * cfrom

End If
End Subof course if you want the values to update if the table is changed then you should enter into target as a formula instead of the value

[Sunshine]

Sorry if I ask a little bit dumb questions sometimes but I'm totally new to VBA. Now my macro looks like this but I don't get it to work :banghead:

Sub ConvertCurrency()Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("b6:i13")) Is Nothing Then
amnt = InputBox("enter ammount to convert")
cfrom = WorksheetFunction.VLookup(Target.Offset(-Target.Row + 5), Sheets("lookuptable").Range("a4:b10"), 2, False) ' change sheet name to suit
cto = WorksheetFunction.VLookup(Target.Offset(, -Target.Column + 1), Sheets("lookuptable").Range("a4:b10"), 2, False)
Target.Value = amnt / cto * cfrom

End If
Function ConvertCurrency(FromAmount As Double, FromCurrency As String, ToCurrency As String, Optional ConversionChart As Range) As Variant
Dim fromDKR As Double, toDKR As Double
Dim amountDKR As Double
If ConversionChart Is Nothing Then Set ConversionChart = Sheet1.Range("A5:I13")

With ConversionChart
On Error Resume Next
fromDKR = ConversionChart.Cells(2, Application.Match(FromCurrency, .Cells(1, 1).EntireRow, 0)).Value
toDKR = ConversionChart.Cells(2, Application.Match(ToCurrency, .Cells(1, 1).EntireRow, 0)).Value
On Error GoTo 0
End With
If fromDKR = 0 Or toDKR = 0 Then
ConvertCurrency = CVErr(xlErrValue)
Else
amountDKR = FromAmount / 100 * fromDKR
ConvertCurrency = 100 / toDKR * amountDKR
End If
End Function
End Sub

[westconn1]

the sub i posted must be on the codepane for the worksheet containing the matrix (example post#1), the correct procedure would be available from the dropdown boxes at the top of the codepane

no sub should ever be within some other sub, functions also should not be within some other procedure

the code as posted, is complete and should work as is, apart from changing the sheet name to suit for the table in post #3, i tested with good results, before posting
if you add additional currencies, you would need to expand the ranges of both the intersect and the lookup

should not DKR be DKK?

[Paul_Hossler]

Let me see if I have this.

1. You select D12
2. Click a worksheet button to run the macro
3. The macro asks "How many INR to convert to EUR? ______________________ (INR in A12, and EUR in D5) (InputBox probably)
4. You enter a number, say 100
5. The macro looks up INR in the other sheet, and finds 9.02 in B9
6. The macro enters 902 in D12 (100 x 9.02)

Correct?

I don't think the currency conversion will work correctly just like that. Do the same above with E12 (GBP) selected, enter 100 and you'll still get 902.

I've always seen the FromCurrency converted to a 'StandardCurrency' and that to a ToCurrency. If for example, your conversion table was 1 EUR = 1.50 USD, 1 EUR = 3.00 DKK, etc. then to convert USD to DKK you'd do

1 USD = (1.00 / 1.50) x 3.00 = 2.00 DKK

The VBA is always the easy part, it's the design and algorithms that are hard.

It would help if you could make a simple math example using the table in your post #3

[westconn1]

I've always seen the FromCurrency converted to a 'StandardCurrency'it appeared to me he is using 100 DKK as the standard in his table, i checked the results from my code with xe.com, not exact, but his conversions were from a few days ago, and also matched the results in his image

[Paul_Hossler]

I'd guess you're correct since that seems to make sense and fit the data.