Convert a cell or multiple cells from SGD value to USD value based on relevant monthly exchange rate

[ameenuksg]

Hi I want to create a VBA to convert 1 or multiple cells in Sheet 1 to the appropriate value by selecting them in column A and applying the relevant monthly exchange rate from Sheet 2.

Would greatly appreciate any help

Sheet 1	Sheet 1	Sheet 2	Sheet 2
A	B	A	B
Net Sales - $	Sales Date	Feb 2020	0.7543
200	3 Mar 2020	Mar 2020	0.7489
300	4 Apr 2020	Apr 2020	0.7123

 

[mse330]

Does the below macro do what you're looking for ?

Sub ApplyRate()

Dim Ar1 As Variant, Ar2 As Variant
Ar1 = Sheets("Sheet2").Range("A1").CurrentRegion '<--- change sheet name as needed
Ar2 = Sheets("Sheet1").Range("A1").CurrentRegion '<--- change sheet name as needed

With CreateObject("scripting.dictionary")
    For x = 2 To UBound(Ar1)
        If Not .exists(Ar1(x, 1)) Then .Add WorksheetFunction.EoMonth(Ar1(x, 1), 0), Ar1(x, 2)
    Next x
    For x = 2 To UBound(Ar2)
        Ar2(x, 1) = Ar2(x, 1) * .Item(WorksheetFunction.EoMonth(Ar2(x, 2), 0))
    Next
End With

Sheets("Sheet1").Range("A1").Resize(UBound(Ar2), UBound(Ar2, 2)) = Ar2

End Sub

 

[ameenuksg]

Thank you so much, it worked wonders! really appreciate it, can't thank you enough

 

[mse330]

Glad to help & thanks for the feedback

 

[ameenuksg]

What if the sales figures in Sheet 1 that I want to convert ranges from column S to AB with a choice of selecting the whole range or just 1 cell.
Also, Sheet 2 data starts in a table from row A3 to B7(after a header(Row 2) and a title(Row 1)) which will be extended further as a new month arrives.
What changes do I need to make to the above VBA?

 

[mse330]

Hey ameenuksg,

Try the below code, it will prompt you to select the range (or multiple ranges) in question where you need to apply the rate. In case you don't want to select the range every time you run the macro, I have added some comments on what to change in the code

Sub ApplyRate_V2()

Dim a, Rg As Range, c As Range
a = Sheets("Sheet2").[A1].CurrentRegion '<--- change sheet name as needed
Set Rg = Application.InputBox("Select your range", Type:=8) ' Comment this line if you want to make your range fixed
'Set Rg = sheets("Sheet1").range("S1:AB1000") 'Uncomment this line if you comment above line

With CreateObject("scripting.dictionary")
    For x = 3 To UBound(a) '<--- 3 is the 3rd row where your first number of data starts in sheet2
        If Not .exists(a(x, 1)) Then .Add WorksheetFunction.EoMonth(a(x, 1), 0), a(x, 2)
    Next x

    For Each c In Rg.SpecialCells(2)
        If IsNumeric(c.Value) And IsDate(c.Offset(, 1).Value) Then
            c.Value = c.Value * .Item(WorksheetFunction.EoMonth(c.Offset(, 1).Value, 0))
        End If
    Next
End With

End Sub

 

[ameenuksg]

The VBA did prompt to select a range, but the figures didn't change. I forgot to mention that the sales date on Sheet 1 is located at Column F ?

 

[mse330]

The figures are formulas ?

 

[ameenuksg]

Nope they are numbers in general format

 

[mse330]

The sales date is always in column F ? Not adjacent to the sales figure as per the first example